Zero in null field

[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

 

[Keith Wilby]

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

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

 

[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.

 

[Ken]

Thanks all, I had been playning with NZ last night but with no success.
The Total: Nz([DailyLAB]+[DailyTT],0) did not work bou I applied it to other
field and it did work.
=Nz([SF-VD_CHG].Form!CHG_Hours,0)
Cheers
Ken

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.

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