The Use NZ For Access And Null Values

NZ(me.thisfieldhasnovalue,"Evaluating Nulls")

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.

examples:

if nz(me.fname,0) <> 0 then

    msgbox "Hello " & fname

else

    msgbox "You do not have a first name".

end if

or another example I would use in a group by query for product sales,

this would be the query column formula:

NewItemCost: nz(me.itemcost,0))

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.

Happy databasing!

Follow me on twitter if you aren't already!   @jhshirley