Recognizing Null Form Controls - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Recognizing Null Form Controls

Ken Rubin

15 months ago

Not a question, but a comment on something I discovered.

In several video lessons, Rick suggested using the following expression to detect null or zero-length strings (ZLS) in a pair of search boxes on a form:
    If searchboxA <> "" AND NOT IsNull(searchboxA) AND searchboxB = "" OR IsNull(searchboxB) Then  [execute code here for an SQL filter where criteria depend on both].

Problem is Access was not recognizing when there was a string in searchboxB, nor when it was null, nor when it was a ZLS.  I practically went out of my mind trying to get this to recognize the null search box.

Researching this problem online, I finally found a successful way to code this, using the Nz function, which recognizes both null and ZLS controls, simplifying my code.  Here's what works for me:
    If Nz(searchboxA) = vbNullString AND Nz(searchboxB) = vbNullString  Then
[execute code].

Though I don't understand this well, apparently the Nz function forces an expression to evaluate to zero even the expression contains a Null value, and also returns O for a ZLS.  This works for me.

	Gary James @Reply 15 months ago
In VB “If” statements are processed from Left to Right. What you could have done was move your NULL string checks ahead of the Empty string tests if that is more likely a problem than empty strings.

	Jeffrey Kraft @Reply 15 months ago
Wouldn't it be better to go. If NOT ISNULL(SearchBoxA) OR SearchBoxA = "" AND Not ISNULL(SearchBoxB) OR SearchBoxB = "" Then ... Execute Stuff Else .... Other stuff it applies End IF I recently had something where I thought ISNULL applied and I was forced to do NZ instead. As somebody here has said. Access is Wierd sometimes.

Ken Rubin OP @Reply

15 months ago

Thanks so much to both of you, Gary and Jeffrey.

Gary, I tried it both ways, and found that only the first expression was evaluated, while Access ignored the 2nd.  I tore out my very few remaining hairs.

Jeffrey, I did try what you’re suggesting, but Access still wouldn’t cooperate.  Using the Nz operator (finally) gave me simpler code that’s rock-solid and behaves completely as expected.  Using Nz,  I was even able to use 3 search boxes, in any combination, to filter on.

Again, thanks so much for your help.

Sincerely,
Ken

Thomas Gonder @Reply

14 months ago

Without a doubt VBA has had me scratching my head sometimes with nulls. For the image below, can you provide an example of what wouldn't work as you would expect? Perhaps when dealing with form control text boxes Access is doing something different? I tried to be true to your original VBA statement as best as I can interpret it.
In my test image below, the result from both If lines are shown, the order doesn't seem to matter.

Beyond text boxes, I've also run across IsEmpty in VBA variables, so I created my own routine that checks for Null and IsEmpty and returns a value for each type of variable for this kind of comparison testing.

	Thomas Gonder @Reply 14 months ago

	Thomas Gonder @Reply 14 months ago
To make your testing easier and save some typing: `Public Function fTwgTestNull(val1 As Variant, val2 As Variant) As Boolean 'If (val1 <> "" And Not IsNull(val1)) And (val2 = "" Or IsNull(val2)) Then If (Not IsNull(val1) And val1 <> "") And (IsNull(val2) Or val2 = "") Then fTwgTestNull = True Else fTwgTestNull = False End If End Function`

	Matt Hall @Reply 14 months ago
I have been using this to evaluate for both. If Nz(searchboxA,"")<>"" AND Nz(searchboxB,"")<>"" Then...

	Thomas Gonder @Reply 14 months ago
@Matt Yes, NZ does a good job for form controls and data fields (not so good with arrays), I was curious to see what was giving Ken such a problem that required hair pulling. I suspect it was a parenthesis problem (or lack thereof).

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View