Parameter in Crosstab Query 1

[wonderwhy]

I've searched and found a million threads with a similar problem, but I can't find an answer that I can understand.

Here's the problem:

I have a crosstab query to which I would like to add 2 parameters. This crosstab query is based on another (regular) query. The fields to which I want to add parameters are not in the crosstab query - only in the original query. The fields are formatted date fields like this:

Year: Format([HandHygieneDate],"yyyy")
Month: Format([HandHygieneDate],"mmm")

Where/how should I create the parameters so that when the crosstab query is run it asks for the Year and the Month?

Thanks,

Julia

 

[PHV]

In the original query, seems that Year and Month aren't parameters but calculated fields with bad (reserved) names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wonderwhy]

Thanks, PH. It doesn't matter if I change the names of the formatted/'calculated' fields it still doesn't work. By the way, the parameters in the original query look like this:

Like [Enter Year]
Like [Enter Month "mmm"]

Thanks for any help,

Julia

 

[PHV]

So, better.
In the SQL pane when in query design view for the crosstab query, insert the following line at the top:
PARAMETERS [Enter Year] TEXT(4), [Enter Month "mmm"] TEXT(4);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wonderwhy]

What is the (4)?

This worked wonderful, btw. I appreciate your help!

Julia

 

[wonderwhy]

also...why couldn't I do this in the parameter box (choose parameters from the query menu)? I actually did that but it didn't work. Now after adding the sql the parameters also show up in the parameter box. I don't get that, but I'm glad that it is working.

Thanks!

 

[PHV]

The (4) is the size.
Crosstab query is a weird animal: it don't like subqueries, undeclared parameters, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244