Full Version: Null checking on comboboxes
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
rsmccli
On many of my comboboxes and some date textboxes I have code that checks if anything has been entered, and only to execute whatever code if something has. Usually it looks somthing like this (minus errorhandling):

CODE
Private Sub cbo1_AfterUpdate()

If Len(Me.cbo1) <> 0 Then

    'Do stuff

Else

End If



End Sub


This has always seemed to work properly for me, meaning if the control is Null, then it skips to the "Else" portion. Would it be better to replace this with something like:

CODE
Private Sub cbo1_AfterUpdate()

If Not IsNull(Me.cbo1) Then

   'Do stuff

Else

End If



End Sub


or does this not make any difference? Just looking for some insight as to whether this may cause me problems in the future. Thanks.
Jack Cowley
This code:

If Len(Me.MyCombo & vbNullString) = 0 Then

will check for both a zero length string and Null. Going to the Else statement is so fast that it should not be a consideration when writing your code...

My 3 cents worth..

Jack
datAdrenaline
I personally use the Len() method a lot ...

If Len(Me.TextBox & "") > 0 Then ...

If Len(Me.ComboBox & "") > 0 Then ...

Amazingling enough, you can even use ...

If Len(Me.Textbox) Then ...

Since Len will return a Null if a Null is passed, and a Null is neither True or False, so the Else branch will be taken by VBA. Click Here to learn more about Nulls and their behavior.

....

There is really no harm in checking for Null with the IsNull(), however some caveats are this:
[*] IsNull() will ONLY check for a Null value, where as using Len(), you check for a Null or a Zero Length String. When dealing with text fields that allow ZLS's, this can be the source of many hours of debugging to figure out why a control, bound to a field, which LOOKS blank (Null) does not branch your code correctly!! ...
[*] A combo box returns a STRING and will only return a Null if no value is selected OR, if you use the .Column() property, the reference Row or Column index is not valid. Note that VBA tends to coerce where needed so ...

Dim x As Long
x = Me.SomeCombo

is completely valid if SomeCombo's bound column is a number because VBA will know your intent. However, when trying to test for a blank value, especially when using the .Column() property, you run the risk of having the combo return a ZLS (note: a column will coerce Null fields values to a ZLS), and thus a Null check will be useless ...

{the same concept is true with List Boxes}

.....

When checking the validity of values, in addition to Len() I will also use:

IsNumeric()
IsDate()

As they both can handle just about anything being passed to them and gives you a bit more info ... so if you are checking for a date and the user enters "1/32/2008", IsDate() will return False, while Len() and IsNull() will return True ...

.....

Does this info help?
Jack Cowley
Brent -

I never fail to learn something from reading one of your posts, to wit, "(note: a column will coerce Null fields values to a ZLS), and thus a Null check will be useless ... " Now all I have to do is remember it!!!

As an aside... Did you get an email notification of this post? Seems I am not getting any and I want to be sure it is not at this end before checking with Gord...

Thank your for expertise!!

Jack
rsmccli
Indeed, this info does help. Thanks alot!
datAdrenaline
Hey Jack -

>> Thank your for expertise!! <<
thumbup.gif Thanks Jack!

>> Did you get an email notification of this post? <<
I am not sure as I am not a home to check .. dazed.gif ..

But ... I DO know that if replies come through the Quick Reply, you will often NOT get email notifs. I have seen this topic (frustration) often talked about in the VIP forum. I think replies STOP as soon as the OP is Not the person being replied too ... BUT ... I do have a LOT more consistency in getting an email IF an responder replies directly to me (ie: the "Reply" link to the side of a post). Also, if I add a thread to my favorites, I seem to get emails as well ... but then I have to manage my "Favorites", which is not desirable for me!! ...

Please check with Gord as this is, and has been, an on going issue ... its actually something that was talked about during casual conversation at the MVP summit, and has been mentioned as a reason why some have not joined or participated more.

... gotta run!! ...
datAdrenaline
Awesome!!! ... glad to help out! thumbup.gif
Jack Cowley
Thanks Brent! FYI I have always gotten emails if they respond to my post (not the quick reply) but it has not worked for me for a few days now. I have put in a request to Admin to see if something is going on at their end.

Thanks for letting me know this came up at the summit as I agree with the general consensus...if there is a problem it needs to be fixed!!

Jack
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.