zeros

[mtress]

I want blank records to return a zero in either a report
or the crosstab query the reports based on. Is there a
simple way to do this?

 

[Allen Browne]

Use Nz() to supply a value to use for Null.

Open your crosstab query in SQL View.

You will see something like:
Sum([Amount]) AS SumOfAmount

Change it to:
Nz(Sum([Amount]),0) AS SumOfAmount

Better still:
CCur(Nz(Sum([Amount]),0)) AS SumOfAmount

 

[mtress]

-----Original Message-----
I want blank records to return a zero in either a report
or the crosstab query the reports based on. Is there a
simple way to do this?
.
Never mind. Figured it out. Seems as if you can't

build an Nz Function in a crosstab query. Or at last not
in the value field of a crosstab. So I did a select
query based on crosstab and was then able to build my
expression from there.

 

[mtress]

-----Original Message-----
Use Nz() to supply a value to use for Null.

Open your crosstab query in SQL View.

You will see something like:
Sum([Amount]) AS SumOfAmount

Change it to:
Nz(Sum([Amount]),0) AS SumOfAmount

Better still:
CCur(Nz(Sum([Amount]),0)) AS SumOfAmount
Thank you very much.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want blank records to return a zero in either a report
or the crosstab query the reports based on. Is there a
simple way to do this?

.