Disclaimer: The information on this page comes from my misused and abused brain
that has suffered several concussions! So read at your own risk. We are not responsible
if you use something here that reformats your hard drive, deletes and writes over
your family photos, or distributes your email address to every spammer on the planet,
The Len Function returns the number (a Long Integer) of
alphanumeric characters of the value in parentheses.
Examples: Len("dog") returns 3, Len("horse")
returns 5, and Len("a horse") returns 7 because the Len Function counts the blank spaces but not the quotation
Len(2.5) usually returns 3, and
Len($1.12) usually returns 4 because Len counts the decimal
point. I wrote "usually" because your TextBox may only allow two decimal points
but there may be any number of decimal numbers not shown and Len counts them all.
So we want to be careful when using the Len Function
with Controls containing numeric data types (that's Geek Speek for "numbers").
The Len Function is often used to ensure
that there is a value in a TextBox or ComboBox. Here are some common ways it is
used to do this:
Len(Me.txtMyTextBox & vbNullString) - or -
Len(Me.MyTextBox & "")
vbNullString is used to save a little
memory and time because Access creates a zero length string whenever you use "" and
vbNullString runs many times faster in code. The
vbNullString Function does not create a new zero length string but
what you use is your preference.
Sample uses in code:
Here some action is taken if a TextBox is empty:
If Len(Me.MyTextBox & vbNullString) = 0 Then
' Some of your brilliant
Here some action is taken if a TextBox is not empty:
If Len(Me.MyTextBox & vbNullString) > 0 Then
' Some more brilliant
The Len Function is also used to parse strings, but that
is another subject.
The Nz Function is your Null zapper. Nulls are those evil gremlins that mean
nothing, equal nothing, and cause errors everywhere they are found. Nulls
occur when no value has been entered into a field. Null means "I don't know."
It is not zero nor is it equal to zero. Null is not even equal to itself!
Fortunately the Nz Function can handle nulls easily.
To use the Nz Function, put parenteses around the name of the control or object
that may be null. Then you insert what you want to get if the control or object
is Null like this:
If the TextBox contains a Null Value, the Nz function returns 0. If this was your
Nz(Me.MyTextBox, "Oh, No! A Null!")
The Nz Function would return the string "Oh, No! A Null!".
Whatever you put after the comma, that is what you will get. The Null value of the
TextBox has not been changed, but the Nz Function saved
you from getting an ugly error message and kept your code from crashing.
Nz is often used like this:
If Nz(Me.MyTextBox, 0) = 0 Then
' Code that actually does something here
The Nz Function does not do anything with empty strings.
It just passes along the "" value without a message, which can really cause problems,
expecially if you are doing payroll and there should have been a name there! A string
value of "" is called a Zero Length String.
A zero length string is a string that has, well uh, zero lenth.
You can check for both a Null and a zero length string
with the following code:
If Me.MyTextBox & "" = "" Then
If the TextBox is Null, adding "" will make it equal "". If the TextBox contains
a zero length string adding a zero length string to a zero length string will result
in a zero length string. So this method checks for both nulls and zero length strings.
But, unlike the Nz Function, you have to write more code
to handle the value.
BTW, guess what Nz returns in a Query: a zero length string! Definitely not
good when you are doing payroll!
We use Microsoft Azure SQL Databases to provide safe and affordable 24/7 internet access to your company's vital information.
Contact us for a Free Consultation, feedback, or more information:
Page layouts based on Matthew James Taylor's Perfect multi-column CSS liquid layouts