Report shows null values, how to convert to a zero

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DBenedict - 21 Dec 2005 03:19 GMT

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.

Reply to this Message

Allen Browne - 21 Dec 2005 03:43 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 6 lines]
> "0"
> without affecting the column above that actually has a number.

Reply to this Message