Crosstab Query can't be Parameter Query? HELP! 4

[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

 

[beetee]

Try this:

forms![MainSwitchboard]![dtmCurrentMonthEnd]'

i.e. remove the LSB and RSB (left, right square bracket) from 'forms'

 

[shannonp]

Go to Query -> Paremeters

Add two paremeters "[Forms]![YourFormName]![dtmStartMonth]" and "[Forms]![YourFormName]![dtmEndMonth]" as two seperate paremeter values (on seperate lines). Then just choose the field type appropriately.

Once this is done run the query (of course making sure that your form is open) and hopefully it'll work.

 

[ahmun]

shannon,

adding the parameters did the trick. thanks! Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[uludude1]

Shannonp1, I have the same problem as ahmun, but what works for you didnt work for me. I have a form with three fields, 2 of which are are numbers for a between
form![field1] and form![field2], and then a sales person form![txtSales]
when i add these to the query parameters

Between [Forms]![ParametersForm]![TxtStartDate] And [Forms]![ParametersForm]![TxtEndDate]

and
[Forms]![ParametersForm]![TxtSales]
i get the same message, and i added it to the parameters box, but the same thing happened. Any other tips, or anything else I might be doing wrong? Thanks,
Dave


Here is the SQL:


TRANSFORM Count(UserTable01.FirmFile) AS CountOfFirmFile
SELECT UserTable01.USR4VAL, UserTable01.ORDDATE, UserTable01.USR1VAL, Count(UserTable01.FirmFile) AS [Total Of FirmFile]
FROM UserTable01
WHERE (((UserTable01.ORDDATE) Between [Forms]![ParametersForm]![TxtStartDate] And [Forms]![ParametersForm]![TxtEndDate]) AND ((UserTable01.USR1VAL)=[Forms]![ParametersForm]![TxtSales]))
GROUP BY UserTable01.USR4VAL, UserTable01.ORDDATE, UserTable01.USR1VAL
PIVOT UserTable01.PURCREFI;
TRANSFORM Count(UserTable01.FirmFile) AS CountOfFirmFile
SELECT UserTable01.USR4VAL, UserTable01.ORDDATE, UserTable01.USR1VAL, Count(UserTable01.FirmFile) AS [Total Of FirmFile]
FROM UserTable01
WHERE (((UserTable01.ORDDATE) Between [Forms]![ParametersForm]![TxtStartDate] And [Forms]![ParametersForm]![TxtEndDate]) AND ((UserTable01.USR1VAL)=[Forms]![ParametersForm]![TxtSales]))
GROUP BY UserTable01.USR4VAL, UserTable01.ORDDATE, UserTable01.USR1VAL
PIVOT UserTable01.PURCREFI;

 

[uludude1]

Disregard above post!!!!

I got it to work. Thanks so very much shannonp, ive been working on this one for a while and this is a huge help.
THanks again,
Dave

 

[shannonp]

No probs

 

[zinja]

I have slightly different problem. I got the CrossTab query to run with no problem, but it displays all records and not just the ones that meet the parameters I typed in the parameters section. I have a form that has many fields on it, the ones I am interested in are Wood (combo box) and Size (combo box). I have a command button that sparks the query. Here is my SQL from my crosstab query. I do not get an error message, but it gets all the items and not just the ones that have matching values for Wood.Value and Size.Value. Please help!

Begin SQL:

PARAMETERS FORMS![OrderSummary]![OrderDetails]![ProductType] Text ( 255 ), FORMS![OrderSummary]![OrderDetails]![Size] Text ( 255 );
TRANSFORM Min(Inventory.QuantityOnHand) AS MinOfQuantityOnHand
SELECT Inventory.ProductType, Inventory.Grade, Inventory.Size
FROM Inventory
GROUP BY Inventory.ProductType, Inventory.Grade, Inventory.Size
PIVOT Inventory.Length;