Display 0.00 when a column is missing an entry

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

Guest

I have a productivity report (money generated) by sales people. The columns
are different categories of sales. Entries for some columns are missing
because the sales person was not active with that line. The value of the
column is probably Null in that case because the left outer join in the
report query from the sales person to that line of goods finds no join. The
customer wants to see "0.00" in those fields. As others have pointed out,
using a conditional formatting (IIf) produces a text entry which interfers
with column totals. Some have suggested using a hidden column to do the
actual column sums. That is not practical because of the size of the report
and if there is no way to do this, I'm just going to tell the customer I
can't do it. Summary: Place "0.00" in report fields that do not return a
numeric value. Can this be done? Many thanks...
 
You can use the Format Property to display almost any value if the field
value is null. Check help on Format Property Numeric.
 
You can just set the Format property of the text boxes, e.g.:
#,##0.00;-#,##0.00;#,##0.00;#,##0.00
The 4th item indicates what to display for null

Alternatively, you could use Nz() in the source query, and typecast the
result. For example, if you switch your query to SQL View (View menu), and
see this expression:
Sum([Amount]) AS SumOfAmount
change it to:
CCur(Nz(Sum([Amount]),0)) AS SumOfAmount

If you are struggling with getting Access to understand your data types
correctly, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
Thanks, I'm indebted to you once more. Had forgotten those format details.

Allen Browne said:
You can just set the Format property of the text boxes, e.g.:
#,##0.00;-#,##0.00;#,##0.00;#,##0.00
The 4th item indicates what to display for null

Alternatively, you could use Nz() in the source query, and typecast the
result. For example, if you switch your query to SQL View (View menu), and
see this expression:
Sum([Amount]) AS SumOfAmount
change it to:
CCur(Nz(Sum([Amount]),0)) AS SumOfAmount

If you are struggling with getting Access to understand your data types
correctly, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

richardb said:
I have a productivity report (money generated) by sales people. The columns
are different categories of sales. Entries for some columns are missing
because the sales person was not active with that line. The value of the
column is probably Null in that case because the left outer join in the
report query from the sales person to that line of goods finds no join.
The
customer wants to see "0.00" in those fields. As others have pointed out,
using a conditional formatting (IIf) produces a text entry which interfers
with column totals. Some have suggested using a hidden column to do the
actual column sums. That is not practical because of the size of the
report
and if there is no way to do this, I'm just going to tell the customer I
can't do it. Summary: Place "0.00" in report fields that do not return a
numeric value. Can this be done? Many thanks...
Click to expand...
Click to expand...
 
Back
Top