Substituting null values with a zero on a report?

[chuckles1066]

How?

I have a query which provides the data for a report.

A lot of the fields in the query don't have data and I'd like those reported as zero rather than appearing as empty on my report.

For example (just to clarify), my query returns all employee activity during a month.....hours worked, sick hours, holiday hours, training hours etc etc.

Now obviously, not all those fields have a value (an employee may not have taken holiday last month for example).....I just need that to show on my report as 0.00.

I tried messing about with an =IIf statement in the control source part but Access protested about a circular reference.

Any guidance much appreciated.

 

[Joe4]

In your query, use the NZ function in a calculated expression, which tells it what to return in the event of a null value, i.e.

My_Hours_Worked: NZ([Hours_Worked],0)

Then just return this calculated value on your report.

 

[chuckles1066]

Brilliant, thank you very much!

 

[Joe4]

Your welcome.

One caveat. By default, the NZ function returns a text value. This might not be a big deal if you are just reporting on it. But if you need to format it in some number format, or if you need to use it in computations, you may need it to be numeric.

You can easily coerce it to be a number by adding zero to it, i.e.

My_Hours_Worked: NZ([Hours_Worked],0)+0