Empty unbound combo box won't return null values

Posted on 2009-04-16
Medium Priority
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
  • 2
  • 2
LVL 65

Expert Comment

ID: 24157948
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

peter57r earned 500 total points
ID: 24157957
You set the criteria to something like...

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

Author Comment

ID: 24158147
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

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

Author Comment

ID: 24158709
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!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

755 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