Rivercity Technology Services LTD Logo
HomeAbout Us
Technology Services
Icon showing a support technician
IT Support Services
Cybersecurity Risk Management at one predictable flat rate.
Icon of light bulbs on a laptop screen
IT Consulting
Business optimization through the smart use of technology.
Icon showing a hand holding a phone
Business Phone Services
VoIP Telephone solutions from RCT. 
Icon showing a database and a cloud
Backups & Recovery
Cloud & On Premise - ready to recover!
Icon showing computer code on a monitor
Software Development
Web & Mobile App development
Icon showing website wireframes
Website Development & Hosting
Web design and full hosting & maintenance packages!
Icon showing an envelope being opened
Modern Email Management
Microsoft 365 email provisioning, security & management.
“You’re giving me the ‘it’s not you, it’s me’ routine? I invented ‘it’s not you, it’s me.’ Nobody tells me it’s them not me; if it’s anybody, it’s me.”
- George Costanza
Learning CenterContact Us
Book A Consultation
Illustration of a person writing a blog post with a pencil

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

Jeff Shirley
Article Written by Jeff Shirley
Related Articles
Featured image
Your Guide to Managed Website Hosting
Creating a website is only the first step. Keeping it online and accessible to visitors 24/7 involves a lot of additional work. Hosting your site means choosing...
2022 - Copyright, All Rights Reserved
crossmenu