Are Crosstab Queries responsible this Access Error Msg?

[Paul Howarth]

I have no problem when I use the following date filter with a Simple Query:

Between [StartDate] And [EndDate]


But I am running into an impasse when I have a query which is referencing a Crosstab query.

I want to filter the results between two dates.

For example, the following works just fine - even when a Crosstab Query is involved:

Between #1/1/2012# And #3/1/2012#

But of course I would prefer to use a variable rather than "hardcode" the dates. But when a Crosstab Query is involved and I use something like the following:


Between [StartDate] And [EndDate]

I always get this error message:

"The Microsoft Access database engine does not recognize '[StartDate]' as a valid field name or expression."

Can't figure out a solution.

[Seth Schrock]

I have run into that when using the Forms!frmFormName!ControlName reference in the WHERE clause of a crosstab query. Trying reading this link: Error when running crosstab query with a parameter. I know the situation is slightly different, but it might still apply.

[Paul Howarth]

That link provides the solution.
Thanks!

[Paul Howarth]

@Seth Schrock
When I open the Query after setting the StartDate and EndDate parameters, it prompts me once for StartDate and EndDate and displays the data just fine.
When I open the Report that uses the same Query, I am prompted multiple times for StartDate and EndDate. Wierd.
Any suggestions?

(For now, my dumb workaround is to press "Cancel" for the first 3 StartDate prompts and then to input StartDate and EndDate just once.

[Seth Schrock]

As this question doesn't match the title of the thread, it would be better to ask this in a new thread. It will also probably get more attention as a new thread then it would in here. Feel free to link to this thread.

[zmbd]

Paul Howarth:
Respectfully, Seth is correct in that you should start a new thread; however, when you do so, you may/should consider placing a link back to this thread.
For your convenience you can cut and paste the following into your new thread:[url=http://bytes.com/topic/access/answers/946707-crosstab-queries-responsible-access-error-msg]Are Crosstab Queries responsible this Access Error Msg?[/url]