parameter selection in crosstab query

[Randy]

I have a simple crosstab query. the column heading is an aging based upon the invoice date vrs a date to be entered by the user. so for example the invoice date is 12/10/2009. The user enters 12/31/2009 so the query would "age" this as 9 - 30 days old. However if the user entered say 12/15/2009 then it would show as 1 - 8 days old. etc.

agingateDiff("d",[enter date MM/DD/YYYY],[DUE_DATE]))>0,"Not Due, DateDiff("d",[enter date MM/DD/YYYY],[DUE_DATE])) between 1 and 8,"1 - 8",

basically it is just a simple 6 nested "iif" statment to age the invoices from not due, 1-8, 9-30, 31-60, 61-90 & >90

if I run it as a select query no problem, it asks for the "Enter Date MM/DD/YYY and you can enter any date and the query returns the correct answer.

However as soon as I make it a crosstab query and choose "aging" as my column I get this error message.

The Microsoft Jet database engine does not recognize [Enter Date MM/DD/YYYY] as a valid field name or expression.

I tried creating a select query with the parameter item in it, then base the crosstab query on the select query, same error message.

any suggestions?

 

[pbaldy]

Does this help?

http://support.microsoft.com/default.aspx?scid=kb;en-us;209778

 

[themurph2000]

Does this help?

http://support.microsoft.com/default.aspx?scid=kb;en-us;209778

Somehow, I always forget about that Query Parameters box, but that's what I needed for mine because my crosstab requires a date range. Thanks!

 

[pbaldy]

Glad it helped you Patrick!

 

[themurph2000]

Glad it helped you Patrick!

Although I was hoping to feed the dates in through a form. Is that possible for a crosstab query? I have a report attached to another query that creates a grid-like report (spreadsheet) that uses the dates for the column headings and displays the data in a crosstab.

 

[pbaldy]

Sure you can; I suspect you'll find that the query runs fine but the report pops parameter boxes. The problem you're probably having is due to the field names changing. When you first set up the report, you have a field name of let's say "1/22/10". When you run the report tomorrow, the field name is "1/23/10". The report barfs because it's looking for the other field name. There's a method here:

http://support.microsoft.com/kb/328320

though I use what I think is a simpler method from the Access Developer's Handbook, if you have that handy.