What Is That?--Geek Speak Definitions, Descriptions, and Pontifications

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, etc.

Len Function

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 marks.

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 code here

Here some action is taken if a TextBox is not empty:

    If Len(Me.MyTextBox & vbNullString) > 0 Then

        ' Some more brilliant code here

The Len Function is also used to parse strings, but that is another subject.

Nz Function

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:

Nz(Me.MyTextBox, 0)

If the TextBox contains a Null Value, the Nz function returns 0. If this was your code:

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!

Login Status: Not logged in.
Login      Free Registration