Problem with Query criteria and Crosstab 3

[dawnd3]

Hi there,

I have a crosstab query that is built on a union query that is built on a series of other queries. It all worked great until I put a criteria in one of the underlying queries to filter the date based on the user input.

I have in the criteria field for the date:

>=[forms]![frmConfig]![dtmBegDate] And <=[forms]![frmConfig]![dtmEndDate]

This is the exact error message I get:

The microsoft jet engine does not recognize >=[forms]![frmConfig]![dtmBegDate] as a valid field name or expression.

This ONLY happens when I run the crosstab, all of the other queries still work with this filter.

Thanks for your help,

Dawn

http://www.calliopeconsulting.net

 

[fnofal]

I think that you need to use Like before your criteria
>=[forms]![frmConfig]![dtmBegDate] And <=[forms]![frmConfig]![dtmEndDate]

 

[dhookom]

Crosstab queries require that you specify the data type of all parameters. Select Query|Parameters and enter
[forms]![frmConfig]![dtmBegDate] Date/Time
[forms]![frmConfig]![dtmEndDate] Date/Time

Duane
MS Access MVP

 

[dawnd3]

Duane, just to clarify, do I put this in the original query (the first query that leads to the crosstab) in the criteria for the date? Like this?

>= [forms]![frmConfig]![dtmBegDate] Date/Time AND <= [forms]![frmConfig]![dtmEndDate] Date/Time

It is telling me that the expression contains an invalid syntax. What am I doing wrong?

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

You already have your criteria set correctly. You need to select query|parameters from the menu and enter what I suggested earlier. I think you can do this in either the select query or the crosstab query.

Duane
MS Access MVP

 

[dawnd3]

Duane, you are the bomb!!! Thank you. I didn't realize you meant parameters on the query menu...I have never used that.

Thank you!

Dawn

http://www.calliopeconsulting.net