NZ
Using the NZ Function
In this tutorial you will learn how to use the NZ Function.
The NZ function can be used to return zero, a zero-length string (" "), or another specified value when a Variant is Null.
=NZ( value, valueIfNull )
value
valueIfNull
Take an example Table of Customers.
CustomerT
| CustomerID | FirstName | LastName | Phone | CustomerSince | CreditLimit | DaysGrace | IsActive |
| 1 | Richard | Rost | 7165557777 | 01/01/2012 | 1001 | 1 | True |
| 2 | Joe | Smith | 7165553434 | 01/03/1990 | 500 | 5 | True |
| 3 | Suzan | Jones | 5624365146 | 02/01/1998 | 1500 | 2 | False |
| 4 | Alan | Watson | 4193891212 | 17/05/2012 | 50 | 3 | True |
| 5 | Anna | Picore | 713236479 | 05/05/2005 | 1020 | 4 | True |
| 6 | Ronald | Simms | 7807465468 | 04/04/1992 | 50 | 2 | True |
| 7 | Donald | Barker | 4164415555 | 01/03/1998 | 250 | 5 | True |
| 8 | Benny | Sperduti | 7167267262 | 06/04/1999 | 1600 | 7 | False |
| 9 | Pete | Smith | 5645597777 | 04/04/2000 | 500 | 3 | False |
| 10 | Barbra | Peters | 8292827288 | 31/12/2005 | 50 | 1 | True |
| 11 | Joe | Jones | 5495465466 | 01/06/2004 | 500 | 3 | True |
| 14 | Pezz | Dispo | | 26/09/2012 | 0 | 4 | True |
| 16 | Bob | Smith | | 04/10/2012 | 0 | 9 | True |
If we wanted to check if there was a Phone number for Pezz we could.
tblNames
| CustomerID | FirstName | LastName | Phone | CustomerSince | CreditLimit | DaysGrace | IsActive |
| 14 | Pezz | Dispo | | 26/09/2012 | 0 | 4 | True |
The result is "".
 Query
The results using a Query would be
SELECT NZ([Phone], "") FROM tblNames;
 FORM
In a Form you could set the Default Value of a Textbox to
=NZ([Phone], "")
 VBA
In a Form add a TEXTBOX control and rename it "txtNZ", then you could add the following in the Load Event.
Private Sub Form_Load()
txtNZ = NZ([Phone], "")
End Sub
Courses
Access Expert 25
 Search
You can find more about this by searching the website.
By: Alex Hedley
Click here to sign up for more FREE tips
| 
