Family Password Habits Worth Teaching
Every September, kids come home with a fresh stack of school supplies and a fresh set of logins, a school portal password, a login for the library system, maybe...
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
