Parameter in Group By

[Ferran]

I've tried many ways without success and I wonder if it is possible (and how)
with FP 2003 and FPSE to pass a parameter to a group by clause to perform
calculations in different fields. Custom query always gets an error.

Thanks in advance,

 

[Kathleen Anderson [MVP - FrontPage]]

Can you post your Custom Query SQL and tell us what the error message is?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others.

 

[Ferran]

Thanks for your attention.
The custom query is:
SELECT ContactesPendents.CodiAccio,::ContactesPendents.NomVenedor::,
Count(*) AS Pendents
FROM ContactesPendents LEFT JOIN CodisPromocions ON
ContactesPendents.CodiAccio = CodisPromocions.CodiAccio
WHERE (((ContactesPendents.Resultat)="Pendent") AND
((CodisPromocions.Validacio)=-1) AND ((CodisPromocions.Add5)=-1))
GROUP BY ContactesPendents.CodiAccio, ::ContactesPendents.NomVenedor::,
CodisPromocions.Ordre
ORDER BY CodisPromocions.Ordre;

and the message error simply "The custom query contains errors".

Thanks again.

 

[Ferran]

Dear Katheleen,

To be more clear and generalistic than the strange language used in the
query: I'm trying to generate a report in a similar way you create a list.

More clear: with many (say text) parameters, you can use LIKE in the DRW and
send a multiple field form querypassing % as the wildcard for each parameter.
You get the columns you want and it is a very good solution to filter and
list no-massive databases (I am using Access 2002 as DB with FP 2003 on IIS
5.1 and IIS 6 with FPSE). The form can be a beautiful collection of drop
downs. If the parameter is a number, you use 1 TO 1 as the default wildcard
value.

I wonder if there is an "elegant" way to build the same but with the GRUOP
BY clause instead of WHERE. And get a table wth counts, sums and expressions,
in a similar way the cross reference query brings in Access. If fact, When
listing, you can even count the records returned by the DRW using FP_iCount
after </tbody>. So the targeted information is very near. I used the "Insert
parameter" in the DRW custom query but can't go further.

On another hand, I can't put at work a TRANSFORM clause in FP pasting
directly from the Access automatically generated SQL sentence. Am I missing
something?

Thank you again,

Ferran

 

[Kathleen Anderson [MVP - FrontPage]]

Ferran:
I don't believe I've ever seen ':: ::' used as select criteria referring to
fields in the database, only used as reference to form fields on the query
page. That said, my suggestion would be to build a simple query in Access
and then use it in your DRW (as a View).

I'm sorry if this doesn't help,

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others