crosstabs report problems

[rhintintin]

Reports for crosstab queries have a problem updating the data in them. If when the report is created one field is blank and then this field has information added to it, the report does not add this field. If this doesn't make any sense, what i want to do is make the crosstab show a zero if a field is blank, rather than not show the field at all.
The version of access i am using is 2000

 

[LarryDeLaruelle]

rhintintin:

If I read your post correctly, this sounds similar to a problem I had recently.

A poster here suggested using the cross tab query's format in the Column Heading's property. Enter each column header you want to display in double quotes separated by commas.

This will force the query result to display the column header even if there are not data for that column.

As for forcing zeros to display if no data is present, I don't think you can. There is a function (NZ) that works with null's in numeric data to make them count as zeros. However, I have not been able to get it to display a zero if the field is null.

Larry De Laruelle
larry1de@yahoo.com

 

[Guest_imported]

I have the same problem with reports made from a crosstab quey and after trying every possible way I can think of using the Nz function, I still could not get the report to show "0" for field that were blank.

The only way to solve is by using a pivot table instead. In the pivot table "Options" you can tell it to enter a 0 for blank fields.

 

[Quehay]

Getting an alternate value for a null in reports is weird. I've tried a number of "Is Null" formulae to no avail. This works:

[tt]iif(Nz([MyVal]="","0",[MyVal])[/tt]

The true part corresponds to the evaluation of whether the Nz kicks in or not.