Empty unbound combo box won't return null values

Posted on 2009-04-16
Last Modified: 2013-11-28
I have an unbound form that has many unbound text and combo boxes.  After a user has entered/selected the data they want to use to filter the data, they click a button and that runs a query that filters the data as they wanted.  This was not a problem before because all the fields they were using before were required and could never be null.  I was asked to add another field as a filter option but this field is not required so there are many null values.  Now when I run the query, I don't get any lines if that new field has a null.  How do I get all lines of data even if that field is null?

I have a query tied to the on click event on the button.  That querys criteria is set to the unbound text or combo box on the form.  Here is how the criteria is currently written: IIf(IsNull([Forms]![Create_Your_Own_Report_frm]![Combo10]),[Circuit_tbl].[Status],[Forms]![Create_Your_Own_Report_frm]![Combo10]).  This works if the user selects a value from this new combo box obviously because it is only looking for that value.  The trouble is when this new combo box is left blank.  It will not return any lines if this field is null.  I tried to return an "" empty string if this is null and that didn't work.  Any other suggestions?
Question by:HTC1898
    LVL 65

    Expert Comment

    Maybe its being treated as a empty string if you got something in there
    try NZ instead

    so instead of


    do this

    NZ(myfield,"") = ""

    convert nulls, if there to "" then check for ""
    LVL 77

    Accepted Solution

    You set the criteria to something like...

    forms!formname!textboxname or ( forms!formname!textboxname is null)

    Author Comment

    Peter, I thought of trying your solution yesterday and got a wierd error so I assumed I did something wrong.  I tried it again so I could get you the error.  Here is what I changed the criteria to....IIf(IsNull([Forms]![Create_Your_Own_Report_frm]![Combo29]),([Circuit_tbl].[ENCOMP])=[Circuit_tbl].[ENCOMP] Or ([Circuit_tbl].[ENCOMP]) Is Null,[Forms]![Create_Your_Own_Report_frm]![Combo29]).  I am getting a 'You canceled the previous operation' error.

    rockiroads, I tried your solution also and now I get no lines of data.  Here is what I tried....IIf(IsNull([Forms]![Create_Your_Own_Report_frm]![Combo29]),NZ([Circuit_tbl].[ENCOMP],"")="",[Forms]![Create_Your_Own_Report_frm]![Combo29])

    I need to keep the first and last part of the IF statement intact.  It is the middle part of the statement that is giving me trouble.  If the combo box is left blank, then do this, else do this.  The else do this part works because that means the combo box was not left blank that the query filters off whatever the user selected.  If the combo box is left empty, it reaturns all values but no null values.
    LVL 77

    Expert Comment

    I don't have any iif()s in mine.

    Author Comment

    Peter, at first I totally didn't understand your answer.  Then I thought...what the heck, these people know way more then I do.  I'll try it.  What can it hurt?  I removed the entire criteria from that field and put in only what you suggested and VIOLA...It works great!  I can't believe it was that easy.  Thanks so much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now