zeros

  • Thread starter Thread starter mtress
  • Start date Start date
M

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?
 
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
 
-----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
Click to expand...
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.
 
-----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?
Click to expand...


.
Click to expand...
 
Back
Top