Report Using Crosstab Query Causes Errors

[AutoMagic]

I created a report using a crosstab query which has up to 5 different
categories to enter hours. On the report I show all 5 categories.
The problem is not all categories have data so when I open the report
I get the following error:
The Microsoft jet engine does not recognize " as a valid field or
Expression

If I delete all the fields that have no data the report runs OK.
I've tried entering the following formulas in the Control Source field
to fix the problem but no go:

Category1 - (no formula) this works OK if field has data but causes
"jet engine" error above if no data
=NZ([Category1],0) - Displays #error on report if data or no data
=IIf(IsNumeric([Category1]),[Category1],0) - displays 0 whether field
has data or not
=IIf(IsError([Category1]),0,[Category1]) - displays 0 whether field
has data or not

It looks like the only way to fix this is to add some data in all
categories but there must be a fix for this. Does anyone know of a
formula that should work for this report?

 

[Tom Wickerath]

Hi AutoMagic,

The columns available in a crosstab query are not known until the query is
run. You can try Access MVP Duane Hookom's solution here:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11

As an alternative, if the column headings are always the same, then you can
use the Column Headings property of the query to specify fixed columns. In
query design view, click on View | Properties. You should see a dialog box
with "Query Properties" in the blue title bar. In Access 2003, the Column
Headings property is the third one in the list. You enter a comma-delimited
list of values in quotes. Here is an example for the Northwind sample
database (all on one line):

"Beverages","Condiments","Seafood","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce"


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[AutoMagic]

Hi AutoMagic,

The columns available in a crosstab query are not known until the query is
run. You can try Access MVP Duane Hookom's solution here:

   http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11

As an alternative, if the column headings are always the same, then you can
use the Column Headings property of the query to specify fixed columns. In
query design view, click on View | Properties. You should see a dialog box
with "Query Properties" in the blue title bar. In Access 2003, the Column
Headings property is the third one in the list. You enter a comma-delimited
list of values in quotes. Here is an example for the Northwind sample
database (all on one line):

"Beverages","Condiments","Seafood","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce"

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/http://www.access..qbuilt.com/html/expert_contributors.html
__________________________________________

I created a report using a crosstab query which has up to 5 different
categories to enter hours.  On the report I show all 5 categories.
The problem is not all categories have data so when I open the report
I get the following error:
The Microsoft jet engine does not recognize " as a valid field or
Expression

If I delete all the fields that have no data the report runs OK.
I've tried entering the following formulas in the Control Source field
to fix the problem but no go:

Category1 - (no formula) this works OK if field has data but causes
"jet engine" error above if no data
=NZ([Category1],0) - Displays #error on report if data or no data
=IIf(IsNumeric([Category1]),[Category1],0) - displays 0 whether field
has data or not
=IIf(IsError([Category1]),0,[Category1]) - displays 0 whether field
has data or not

It looks like the only way to fix this is to add some data in all
categories but there must be a fix for this.  Does anyone know of a
formula that should work for this report?- Hide quoted text -

- Show quoted text -

Thanks Tom. That did it!

 

[Tom Wickerath]

You're welcome. Glad I could help.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________