469,341 Members | 7,182 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,341 developers. It's quick & easy.

How to filter form when value selected from combobox is null?

I have a combobox which has null as a valid value.I want the form to get filtered when user selects null as a value also.However no filtering happens when the value is null.All I would like to know is whether it is possible to filter if the record selected is null as the filter criteria will be empty string then?I am not posting any code as my question is just that can criteria be null and still filter?
Mar 20 '12 #1
4 4047
2,321 Expert Mod 2GB
The important thing to remember about null is that you cannot compare with/to null.

Null can best be charecterized as a symbol indicating lack of information. You cannot compare "lack of information" to another "lack of information".

What you CAN do however is check for that lack of information. In SQL it can be done like so:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tbl_Example WHERE field_Example IS NULL
In VBA you can check a control for null, like so:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(me.Combobox1) Then
  2.   Msgbox "Please enter information"
  3. End If
You can also safely convert a null to something else. For example I will often use Dlookup to look for whether a value exists like so.
Expand|Select|Wrap|Line Numbers
  1. Dim lngID as long
  2. lngID=nz(Dlookup("ID_CustomerRep","tbl_Orders","ID_Order=27612"),0)
  3. if lngID=0 then
  4.   MsgBox "The record selected does not have any customerRep assigned"
  5. End If

In your case where you filter by a combobox you could simply convert the null value to an empty string. Just remember that null is NOT comparable to anything, even an empty string.

Your filter string could possibly be written like so:
Expand|Select|Wrap|Line Numbers
  1. tx_FieldName=Nz(Forms!frmExample!combobox1,"")
Mar 20 '12 #2
32,182 Expert Mod 16PB
Technically, you can compare a Null to another value. The problem is that it will always return a result of Null (which, when used within an If statement or IIf() function, is treated as False).

This can be used to your advantage sometimes, but in this case I would recommend checking for Null explicitly when the value of the ComboBox is Null.
Mar 20 '12 #3
2,321 Expert Mod 2GB
Sure, there is nothing wrong with writing If Null="Apple" Then, and VBA will even give you a result NULL. The problem as I see it, is that when you perform a comparison, using a comparison operator such as >, <, <>, =, <=, >= you are actually relying on those operators being defined for the set of values they are asked to be used on.

Most of us are so used to seing these operators that we don't really spend time thinking that they are not a universal constant. They actually depend on the types of values they are used on.

While we all know (or at least SHOULD know) how to read and interpret the statement 7>5, it becomes alot more unclear should we look at complex numbers 7 + 5i>= 5+7i, or when it comes to comparing strings.

Now for strings most of us will say that "abcde"="abcde" should equal true. But what about "abcde"="ABCDE"? It depends on the way in which the = operator is specified. In default access VBA the above yields true.

Now we can make the example more complex by using the > or < operators. How about the statement "abc"<"def" (false) or even "abcgaaaaaaaaaaa">"def" (false)? The < and > operators have been defined for working on strings as well.

There is no definition for comparing with null. Imagine I walked up to you on the street with an apple in one hand and my other hand on the back, and asked you whether the item in my left hand is equal to the item in my right hand. You would be unable to answer true or false, you would answer "I do not know", or NULL.
Mar 20 '12 #4
32,182 Expert Mod 16PB
Everything you say is true Smiley (except maybe
"abc"<"def" (false)
), but nevertheless this knowledge can be used to simplify code.

I often use a construct like :
Expand|Select|Wrap|Line Numbers
  1. If Me.txtEntry > "" Then ...
Nothing entered leaves a value of Null, which causes the comparison to resolve to Null and the False branch to be taken. Thus, I can happily use this code to check if anything has been entered into [txtEntry]. It looks like a string comparison, but technically isn't. Just don't ever try to use it with < or = comparison operators ;-)

PS. I don't believe this is useful for HiGu or their situation. I merely mention it for completeness of the subject. While it is almost completely correct to state that such comparisons cannot be carried out, it is not quite 100% accurate, and I simply wanted to fill out the exact situation. It wasn't a criticism of your post which was, in general, very clear and illuminating, explaining the situation very well.
Mar 21 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Les Desser | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.