Nulls and Empty Strings

Sometimes, you want to check to see if a field is either Null or contains an empty string (also known as a "zero-length string"). Empty strings can creep into your tables if you specify Yes to "Allow Zero Length" in the field definition during table design. If you want to be sure you are checking for either one, use a line of code like this:

If IsNull(BusinessName) or BusinessName = "" Then

What a hassle—you have to type the name of the field twice, and the line is confusing to read. There's a much easier way:

If BusinessName & "" = "" Then

This technique uses the concatenation behavior of the "&" operator to our favor. The & operator concatenates two strings together, even if one of them is null (see the section String Handling Techniques later in this chapter). In this case, it concatenates an empty string ("") onto the end of BusinessName. If BusinessName is null, the result will be an empty string. If BusinessName has any string value in it, it will remain unchanged by tacking on an empty string. This behavior allows us to quickly check if a field has either a null or an empty string.

0 0

Post a comment