Nz Function

[JRM]

I have a report that is based on a crosstab query that leaves spaces in
columns with no data. I have tried the Nz function in the value field of the
query (e.g. Nz([payment],0) and in the report fields (same approach) but to
no effect. How do I get Access 2003 to place a zero in the empty columns?

This has been frustrating since I have no problem with using this function
to return zeros in other reports of this type.

Regards

John Morgan

 

[Graham Mandeno]

Hi John

The Nz is happening *before* the query is being pivoted into a crosstab, so
if there are no matching records in the group, there is no NULL to turn into
a zero.

I think the only way around this is to use the Nz function in the
ControlSource of your textbox(es) on your report.

 

[JRM]

Hi Graham,

Thanks for the response. I had already tried adding the NZ function to the
control source of the report but with no results. Any other suggestions?

Regards

John Morgan

Hi John

The Nz is happening *before* the query is being pivoted into a crosstab, so
if there are no matching records in the group, there is no NULL to turn into
a zero.

I think the only way around this is to use the Nz function in the
ControlSource of your textbox(es) on your report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a report that is based on a crosstab query that leaves spaces in
columns with no data. I have tried the Nz function in the value field of
the
query (e.g. Nz([payment],0) and in the report fields (same approach) but
to
no effect. How do I get Access 2003 to place a zero in the empty columns?

This has been frustrating since I have no problem with using this function
to return zeros in other reports of this type.

Regards

John Morgan

 

[Graham Mandeno]

Hi John

That's surprising - my recollection is that crosstabs return Null in empty
cells, but I may be wrong.

Try an expression like this in the ControlSource:

=IIf(IsNumeric([FieldName]), [FieldName], 0 )

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the response. I had already tried adding the NZ function to
the
control source of the report but with no results. Any other suggestions?

Regards

John Morgan

Hi John

The Nz is happening *before* the query is being pivoted into a crosstab,
so
if there are no matching records in the group, there is no NULL to turn
into
a zero.

I think the only way around this is to use the Nz function in the
ControlSource of your textbox(es) on your report.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a report that is based on a crosstab query that leaves spaces in
columns with no data. I have tried the Nz function in the value field
of
the
query (e.g. Nz([payment],0) and in the report fields (same approach)
but
to
no effect. How do I get Access 2003 to place a zero in the empty
columns?

This has been frustrating since I have no problem with using this
function
to return zeros in other reports of this type.

Regards

John Morgan