Nulls in Crosstab queries (Access 97)

Topic

New Reply

I have a crosstab query which has a row header of dates, and column headers of various types of goods, and a value of goods booked out. Some dates don’t have goods within a column header booked out, so I get a Null. I have tried various ways of getting the Value within the crosstab to display 0, instead of Null without success. When I try to add columns later, where there is a null value in one column field, I get an overall Null despite the fact that one of the fields has a value ….. can anyone help??
Regards and thanks.

Reply | Quote

Replies

You can use the Nz function to substitute a 0 for null values: in the query design grid, use Nz([Fieldname],0) instead of [Fieldname].

Does this solve your second problem too? If not, how are you adding columns?

Reply | Quote

Hello Hans, how are you? Thanks for your reply. Had tried NZ in the value column with no success, but have now found another way to get to the data I was wanting. I’m not sure why NZ doesn’t seem to work in crosstab queries…. Many thanks for your help as always.

M

Reply | Quote

Marion,

Since you already found another solution, this is just for the record – perhaps someone else will be able to use this. Nz *does* work in crosstab queries, but in a slightly different way. Enter an expression in the field of the form

V: Nz(Sum([FieldName]),0)

and set the Totals option to Expression.

Have a good weekend.

Reply | Quote