Report shows null values, how to convert to a zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My report comes from a Crosstab query. I have one row Part No. and two
columns, PASS / FAIL. Many rows have data in both columns, but some only
have data in one column.
Example.
Row 1, Part No has a FAIL of 5 and a PASS of 5.
Row 2, Part No has a FAIL of 2 and a PASS of "blank or Null".
I want the Report to show "Row 2 has FAIL of 2 and a PASS of 0".
I want to convert the "blank", Null or no data fields into a the number "0"
without affecting the column above that actually has a number.
 
Open the report in design view, and set the Format property of this text box
to something like this:
0;-0;0;0
The 4th value causes it to show a zero for null.

If you actually want a zero value for calculations (not just display), open
the query in SQL View (View menu, in query design), and add Nz() around the
expression in the TRANSFORM clause. For example, if you see:
TRANSFORM Sum(Table1.Quantity) AS SumOfQuantity
change it to:
TRANSFORM CLng(Nz(Sum(Table1.Quantity),0)) AS SumOfQuantity

The CLng() typecasts so JET interprets the value correctly.
Use CDbl() if you need fractional values, or CCur() for currency.
 
Back
Top