Having searched the forum for help I have come a little way such as declaring my parameters (thanks to Pat's many replies) but I’m still doing something wrong.

My crosstab query works fine with no criteria and also works fine if I do use criteria but don’t format the date. But, I want to search by month from the Incident Date. This is usually as a Short Date dd/mm/yy but as I want to search by month I have therefore formatted it in the query design grid to convert it:


IncidMth: Format([IncDate],"yy/mm")

In the criteria of this field I have:

Between [Start Month yy/mm] And [End Month yy/mm]

In the Query Parameters box I have:

[Start Month yy/mm] Date / Time
[End Month yy/mm] Date / Time


It appears to run ok, but I then get an error message:

“The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.”

I realize that this is the Format on the date field that is causing the problem and also realize I can probably get around it by making another query to convert the date to month, then running this query from that one – but I wondered if there was a way of getting around that. This would work okay in a non-crosstab query, so wonder why it won’t work on a crosstab. Can anyone help please?

*** *** *** *** *** *** ***
I've since tried creating another query to convert the date, then running another query off of that, and it runs, but returns ALL the data rather than by selecting by the criteria I'd entered. http://www.access-programmers.co.uk/ubb/frown.gif

[This message has been edited by Ally (edited 04-23-2002).]

Once you have formatted the date to yy/mm, it is no longer a date. It is a string! Try changing the data type of the parameters to text.

Excellent. Thanks Pat. Why didn't I think of that?! http://www.access-programmers.co.uk/ubb/smile.gif