473,836 Members | 1,261 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

99 New Member
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 4495
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
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
NeoPa
32,584 Recognized Expert Moderator MVP
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
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
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"="ab cde" 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 "abcgaaaaaaaaaa a">"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
NeoPa
32,584 Recognized Expert Moderator MVP
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

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

Similar topics

5
2425
by: Elvis V. | last post by:
Good morning, I have a table with three fields, Buildings, Floors and Rooms. This is what I would like to do; in my form when I select Building1 in my drop down box for Buildings, when I go to the next drop down field which is Floors, all I would like to see is just the floors that belong to Building1 only and then once I have selected a value for Floors, when I go to the next drop down field which is Rooms, all I want to see are the...
7
8543
by: Nicolae Fieraru | last post by:
Hi All, I am trying to change the rowsource of a combobox when I click on it. I played with many events, associated with the form and the combobox, but still haven't figured out what is the way of doing it. I have a table with products, tblProducts, some of them are Active while others are Inactive. The form shows all the products purchased by a customer, both Active and Inactive in a ComboBox, cbProducts. My client wants to view all...
8
12121
by: Zlatko Matiæ | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the combobox. What is the solution? Thank you in advance.
2
1745
by: dchman | last post by:
I have a C# app in VS2005. the form has a datetime picker with valuechanged event. When the form closes, the valuechanged event is fired and an error is thrown in its code when it tries to get a selected value from a combobox which apparently doesn't exist anymore. I get around the problem with some error handling, but what I really want to know, is there a way to cancel the control event handlers on a form when the form is closing? --...
2
1872
by: Les Desser | last post by:
We have a table (Table1) with a Long Integer field that is a key to an AutoNumber field on Table2 There is a 1-to-Many relationship from Table2 to Table1 on these fields. Table1 allows this field to be Null. An Auto-Form based on this table also accepted a null value for the field.
13
2003
by: PW | last post by:
Hi, This is bizarre. I've check and rechecked the spelling, cut and paste the name of the form (when trying to rename the form) and the control yet I still get a parameter prompt when the form runs. I've even deleted the control and started over. This is the code for the recordsource of the form: "SELECT * FROM qryGuestStorePurchases_pw WHERE
1
2133
by: Joel1334 | last post by:
Hi! How can I change forecolor of a selected value in combobox? when I select a value in a combobox and press a button to "activate" what I've selected I want the text to be green and then black when I select another value.
1
1442
by: bullfrog83 | last post by:
In the form header I have four unbound comboboxes that I use to filter records in the detail section. You can select a value in one, two, three or all four of the comboboxes and it doesn't matter the order. Basically, I'm trying to duplicate the filtering feature in excel. Each combobox allows an "All" value (0 being the All's id). On the AfterUpdate event of each filter combobox I only have "Me.Requery" since I have the filtering work done...
3
2534
by: Diana Miller | last post by:
Hi All, I am creating a report from Access 2007. I have a form where the user selects a Month from a combo box and then presses a button to show the sum of all pages worked for the Month selected in the combo box. I am populating the combo box from the database and everything there works great. My problem is this: How do I code the button/combo box to take the value selected by the user in the combo box and then calculate the total...
3
4674
by: Rania Hassan | last post by:
I created JSP page containing a form with two comboboxes, how can I get the selected value from the first combobox to pass it to SQL statement used by the second combobox? here is my code with notes: <%@ page language="java" contentType="text/html; charset=windows-1256" pageEncoding="windows-1256"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <%@ page ...
0
9661
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10582
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10247
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9367
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4005
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3108
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.