Using NZ does ONE evaluation step which gets the data you requested from a field or formula (if it is NOT null), otherwise it will pass in a new value to use in it’s place so the formula or expression does not break due to a NULL. This is required in a lot of scenarios due to the fact you can not evaluate “nothing” to determine a required outcome.
if nz(me.fname,0) <> 0 then
msgbox “Hello ” & fname
msgbox “You do not have a first name”.
or another example I would use in a group by query for product sales,
this would be the query column formula:
In the last example if itemcost was left blank the sum formula would have failed because you can’t sum NULL plus values. but you CAN sum 0 plus values. So now I can do a group by on this query, or a sum on that new field I created. Without the NZ I can not. The real problem is allowing NULLS on a cost field. one should default in 0 and force a value in the property of the field on the form, by using a validation setting of “Is Not Null”. You can also do defaults at the table level, but I rarely endorse that . I like all formulas and defaults and settings done at forms, unless it is the primary key, a timestamp, or a bit field. once in a while I default values for number or text fields on tables, but I find this permanently binding so I prefer to do the default at a form level.
Follow me on twitter if you aren’t already! @jhshirley