Having trouble with Crosstab query

[Gezza]

Hi,
I have a Crosstab query that gives me the message "The Microsoft Jet database does not recognize '[Forms]![frmMachineReports]![cmb_Machine_ID]' as a valid field or expression.". But if I type the criteria directly into the criteria field, the query works fine.

Have I done something wrong?

 

[HiTechCoach]

You may need to define a parameter for this.

 

[Gezza]

Hi, HiTechCoach
Thanks for responding, sorry be how do I define the parameters?

 

[DCrake]

When you get the error is it happening when the form is open or when it is closed?

Or if you try to open this query from an open form other that the one referred to in your query.

David

 

[Gezza]

Hi, DCrake

thanks for your response

"When you get the error is it happening when the form is open or when it is closed?"

When the form is open, with an item selected in the Forms Combo Box

 

[HiTechCoach]

You will have to define a parameter for the form reference


1) click Parameters on the Query menu.
2) Enter the following under Parameters:

[Forms]![frmMachineReports]![cmb_Machine_ID]

Also see:
Error When Running Crosstab Query with a Parameter

Hope this helps ...

 

[Gezza]

Thanks again,

I have entered in the Parameters but the query is still giving me all the data (not filtered by the contents of the Combobox). Is there somthing else I need to do?

 

[Steven Deetz]

I am having a similar problem to Gezza. My cross-tab, and I think it could be happening within Gezza's, is breaking out data from one column and putting it into two columns. In my case, employer deposits for their employee health reimbursement accounts are classified as either an employer generated contribution or an employee withholding from the employee paycheck. My crosstab looks at an employer set of contributions over a user specified time frame.
The problem in the crosstab query occurs when there is no employee withholding during the selected time frame. The table holding the data has an AccountType numeric field. My report is looking for a field from the crosstab displaying employee withholdings.
I have tried an Nz function and an IIf function but the error appears to pop up before the functions are even run. I am thinking about running a piece of code to examine the data table to see if any employee withholdings exist during the user specified time frame and if not create a different way of reporting the data, but this could get messy and I am hoping someone in the forum will have a more elegant solution.
Thanks in advance for any suggestions.

 

[Steven Deetz]

I found my solution to the crosstab problem. In reviewing other posts I came across "Display zero values in a crosstab query" and followed the directions of labeling the fields that I wanted displayed in the crosstab. When there are no values for a field, it still shows in the crosstab. Then I created another query for the report's data source and I use the Nz function to report a 0. Works like a charm and no messy code.