Crosstab query source for a report

[Amileaux]

In order to get months across on my report, I have resorted to using a crosstab query as my source. However, if I don't have data for a certain month I get an error when I try and open the report "The Microsoft Jet Engine Database does not recognize "Nov' as a valid field name or expression". I can open the report up in design view - and the problem is fixed by going an placing zero in the month of Nov. However, I need to figure out how to make this work more "elegantly" since others use this and they may not have data for the month of "Nov". I want all months across to show - regardless if there is data or not. Any thoughts? Maybe I shouldn't have used a crosstab query. Marie

 

[Dugantrain]

Did you re-sort the headings by dragging and dropping or did you use fixed column headings? It seems like fixed headings will allow for Nulls.

*Edit* I just wrote up a quick cross-tab that specifically contains Null values using fixed headings and it seems to work fine, so give that a try if you haven't already done so.

 

[mdmilner]

Try Changing the field value to automatically convert null values to zero using the NZ function.

Sum(NZ([fielname],0)

Mike

 

[Amileaux]

Thank you! Question: Why would changing the "label" or "heading" impact this problem - since I think the problem is that I sometimes have "values" in the fields and sometimes I don't. I'm sure changing to "fixed" column headings will work - I just don't understand why. Anyway - thank you both for your time - I'm going to try both ways. Marie

 

[Dugantrain]

When you create a report based off of a cross-tab, you'll see that you must specify your headings in that report. If you don't fix the headings in the underlying cross-tab, then some headings that were present when you first created it may not show up in the crosstab when run later if there is no data and thus the report will be searching for column headings that don't exist. Fixing your headings in the query, however, will result in those headings always being present, regardless of whether or not there is data.

 

[mdmilner]

Found this while researching a similar problem.
Have a Crosstab that concatenates portions of fields to use as Column Headings, and when one is missing, the Report chokes.

I think I'm being dense and am going to slap my forehead when you answer, but how would you go about "fixing your headings in the query"?

First thought I had was use the crosstab to populate a table.

 

[Dugantrain]

In design view, right-click on the grey portion, go to Properties, go to Column Headings, and then manually type in the column headings that you like (each with double quotes and followed by a comma).