Crosstab Query won't accept Parameters? HELP!

[ahmun]

Hello everybody.

I got a problem and a limited time!

I'm trying to use a parameter query (whose source is a crosstab query) to custom-filter data on a table based on the user's selection of a Pull-down list.

But... I get the error message "The Microsoft Jet database engine does not recognize '[forms]![MainSwitchboard]![dtmCurrentMonthEnd]' as a valid field name or expression."

Can anybody help me get around this problem? Should I save all the values of the crosstab query in a temporary table to avoid this error message (I've used parameter queries on tables with no problem) or is there a simple trick?

MY setup is like so:
I have a table with an Accident Code and in it is a field for teh type of accident. The Accident type comes from a list of five values (A,B,C,D,E)

tblAccidents:
-------------
intAccidentCode
dtmDateofLoss
strAccidentType

I tried creating a crosstab query that looks like this:

qryAccidentsCrosstab:
---------------------
intAccidentCode
dtmDateofLoss
AccType_A
AccType_B
AccType_C
AccType_D
AccType_E

Finally, I created a query that pulls from this crosstab with a date field filtered based on the users selection (the reason I didn't put the date filter directly on the crosstab in the first place is cuz I would make 3 different queries, one for Month to Date, Year to date, project to date..).:

qryAccidents_MonthToDate
------------------------
intAccidentCode
dtmDateofLoss <-- dtmDateofLoss BETWEEN Forms!...dtmStartMonth AND Forms!...dtmEndMonth
AccType_A
AccType_B
etc.
.
.

Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[uludude1]

I have the same problem. It seems that this problem is unique to crosstab queries, because if you remove the TRANSFORM and PIVOT from the SQL, it runs fine. Saving it to a table won't work either. I have been working on this one for a while, and by the lack of responses, I assume no one else has any hints.
Dave

 

[ahmun]

Dave,

Someone posted a very useful tip on my same question that I posted to a different forum... it might help you, too!

thread702-490127

Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once