Crosstab qry OK, report gens "invalid data type" error

[State]

I have a crosstab query that generates counts for deaths (two categories - primary & secondary causes) within age ranges (generates zeros if none). The query generates the results OK. For example:

PCauseofD SCauseofD
X Y
0to10 11to21 22to30 31to49 50to69 70plus
0 0 5 0 0 1

X Z
0to10 11to21 22to30 31to49 50to69 70plus
3 0 0 3 1 0

However, when I attempt to run the report based on the query, I get the "The Microsoft Jet database engine could not execute the SQL statement because it contains a field that has an invalid data type. (Error 3169)" error. The SQL looks OK and the fact that the query works has me puzzled as to why I am getting this message upon executing the report.

Including the two group by's in the query, I have the following two fields:

group by (col heading) field:

AgeGroup: IIf([Age]<=10,"0to10",IIf([Age]>=11 And [Age]<=21,"11to21",IIf([Age]>=22 And [Age]<=30,"22to30",IIf([Age]>=31 And [Age]<=49,"31to49",IIf([Age]>=50 And [Age]<=69,"50to69",IIf([Age]>=70,"70plus"))))))

expression (value) field:

DummyField: nz(Count("x"),0)

The report has the count fields as follows:

=Nz([0to10],0)
=Nz([11to21],0)
etc...

I am not sure if this is part of the problem. Any help would be appreciated. Thank you.

Paul

 

[State]

Update:

I replaced "DummyField: nz(Count("x"),0)" with "DummyField: Count("x")" in the query and was able to generate the report. Now, however, the report will only execute for a certain beginning date in the date range (I am entering a beginning and ending date range to run the query & report). The query generates OK, but the report does not generate if I have a beginning date range of 3/25/02 or later (i.e., today). I checked my data to verify that dates within this range exist, no null, missing, weird data, tried process-of-elimination to remove the data to try to narrow down any problem records, etc... without any luck.

If I enter any beginning date prior to 3/25/02 (i.e., 2/20/02) with any ending date, the report generates OK.

Paul

 

[State]

Update Part II -

Here is another clue to this problem:

Trying to enter a date range with a beginning date of 3/30/02 now generates the following message:

"The Microsoft Jet database engine does not recognize '[0to10]' as a valid field name or expression. (Error 3070)"

Any ideas?

Paul

 

[State]

Identified problem! - need solution!

Eureka! I discovered that if no data exists for one of the age ranges (for example, no daaa for the 0-10 age range), then the query will not generate the 0-10 range column. That is why the report is not generating - it needs this field, but it does not exist.

What I now need to know is how to force a column to generate in the crosstab query even if there is no data (i.e., 0-10 column with all zeros).

Paul

 

[Sohaila Taravati]

Did you set up your column headings? If not, go the the design mode of your crosstab query and the properties of the query and set up your cloumn headings. I think that will solve your problem.

Sohaila

 

[State]

My Column heading field in the crosstab is the following:

AgeGroup: IIf([Age]<=10,"0to10",IIf([Age]>=11 And [Age]<=21,"11to21",IIf([Age]>=22 And [Age]<=30,"22to30",IIf([Age]>=31 And [Age]<=49,"31to49",IIf([Age]>=50 And [Age]<=69,"50to69",IIf([Age]>=70,"70plus"))))))

I tried to list the column headings (in the col headings) property for this col heading field, but if I do this, I get all the columns, but they are blankm (when I run the query). I need a way to have the column show, regardless if there is data in it or not. I think the problem lies with the column heading. I am not sure if there is a way to modify it so that all the columns show, along with their values.

Paul

 

[State]

Problem Solved!

Thank you for your help - I was not entering the column headings EXACTLY as they appear on the resultant query. Once I fixed this, the query & report ran OK. Thanks again.

Paul