Cross-tab query dilemma..

[Blackwidow]

I am using a cross-tab query to select information however I want it to only show me information within a specified time period... so before the query runs I want it to ask between which dates... I have tried putting it in the query but it wont recognise [Between which date?].. I have a funny feeling it has something to do with parameters... but I just cant work out what I am doing wrong!

 

[JimmyS]

It is something to do with the parameters..

With the query open in design view go to Query/Parameters on the menu and put in your parameters and data type - e.g "START DATE" / Date/Time and "END DATE" / Date/Time

Now if you refer to these in the query it should recognise them....

HTH

 

[Blackwidow]

parameters

Okay I did that I have put [week commencing?] in the criteria under attendance date and have also put [week commencing?] with data type date in the query parameters and have managed to get that part of the query to work but when I try and run it through the report it fails saying "The microsoft Jet database engine does not recognise " as a valid field name or expression.

Any ideas??

 

[JimmyS]

Now I think - but i'm not sure, that you may have to create a form to enter your parameter into so that it is open with the date entered when you load up the report -

it might be worth bringing this up in the reports forum to see if anyone there can help...

 

[theatreman]

I had this problem the other day. I used a form to enter the date range but then the crosstab didn't like the form in the criteria field. In the end I used a make table query using the date range form to isolate the data I wanted and then did the cross tab on the new table. It works.

 

[Blackwidow]

I have managed to get round it to by doing an append query and attaching the previous weeks data to tblpreviousAttendance and then a delete one to clear the information out of the tblAttendance... It works too... but it seems a little long winded to get something that should be so simple.... This is the second time I have attempted to put date criteria into a cross-tab query and had to find an alternative way of getting it to work... So if anyone does have the "KNOWLEDGE" then please feel free to share it with the rest of us