Zero in null field

K

Ken

Hi All
One of the fields in my query "Total: ([DailyLAB])+([DailyTT])" displays the
correct numerical value when run.
Is it possible to get it to display a Zero if it after running, finds no
records to display.
The reason for this is that the result is added to a calculated field which
adds 2 fields together, but cannot calculate because the result is null?
Regards
Ken
 
K

Keith Wilby

Ken said:
Hi All
One of the fields in my query "Total: ([DailyLAB])+([DailyTT])" displays
the correct numerical value when run.
Is it possible to get it to display a Zero if it after running, finds no
records to display.
The reason for this is that the result is added to a calculated field
which adds 2 fields together, but cannot calculate because the result is
null?
Regards
Ken
Click to expand...

Try wrapping your fields in a null-to-zero (Nz) function. Details here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp

Regards,
Keith.
www.keithwilby.com
 
B

BruceM

Help also has information about Nz. Note that if you wrap the whole
expression in Nz it will evaluate to 0 if either field is null:
Total: Nz([DailyLAB]+[DailyTT],0)

Using Nz for each field will treat the null field as if it is 0:
Total: Nz([DailyLAB],0) + Nz([DailyTT],0)

If DailyLab is 3 and DailyTT is null, the first expression will evaluate to
0, and the second to 3.
 
Top