Checking for NULL

[Rendfest]

I'm creating an Access Database and I need to check a combobox data feild to see if it has data in it. Does anyone know how to do this? I tried checking for NULL but it just gives me a type missmatch error.

Ren

 

[VBAjedi]

I work in Excel, but this should transfer across:

I use the LEN function to see if the combobox contains any characters, i.e.:

If LEN(MyComboBox.Value) > 0 Then
   ' Box has data in it
Else
   ' Box does not have data in it
End If

Let me know if that works for you!


VBAjedi

 

[bryanbayfield]

IsNull Function


Returns a Boolean value that indicates whether an expression contains no valid data (Null).

Syntax

IsNull(expression)

The required expression argument is a Variant containing a numeric expression or string expression.

Remarks

IsNull returns True if expression is Null; otherwise, IsNull returns False. If expression consists of more than one variable, Null in any constituent variable causes True to be returned for the entire expression.

The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string ("&quot, which is sometimes referred to as a null string.

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.

 

[VBAjedi]

Bryan,

I usually choose LEN over ISNULL for the simple reason that LEN more closely represents what a user can see. For example, if a cell contains a formula that currently has no visible result, LEN flags the cell as being empty, while ISNULL does not. However, neither will identify if there is only a space in the cell (which looks blank to the user and usually isn't valid data).

Both methods work equally well 90% of the time. It just depends on your application and what you want to happen that other 10% of the time. . .

VBAjedi

 

[TonyJollans]

Hi VBAJedi,

You are right about length zero often being a better check than Empty (cells are never null) in Excel, but Ren asked about Access and Bryan is correct to highlight the IsNull function over the virtually useless &quot;If x = Null&quot; construct which I suspect Ren was trying to use.

Enjoy,
Tony

 

[VBAjedi]

Aha - right-o, Tony! My mind is permanently stuck in an Excel viewpoint. . .

Thanks for catching that.

VBAjedi

 

[TonyJollans]

My mind seems permanently stuck, too.

 

[Rendfest]

Thanks guys for you help, the LEN function works perfectly. I didn't even now that existed. I did try using x = Null, and isNull.

Ren

Sometimes I feel like I've wandered into a cave without a flashlight.