How to view all records from combo box on form

toalexsandr
toalexsandr used Ask the Experts™
on
I have a combo box on a form that passes 6 different annex number sets as parameters to the linked query. These numbers are 0600000, 1502000, 1602000, 1700000, and 1800000. The following line works perfect when selecting those numbers individually and after update, subform query gets refreshed and updated. 
[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]

Open in new window

I also need to see all records from the query not just when I select individual annexes, and this is where I am having a problem. Currently this is the code I am using as criteria in the query, but its not returning any records when in the dropbox I select empty option, or even a * option. 
IIf(IsNull([forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]),"*",[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop])

Open in new window

Can anyone help me with what code I need to have these annexes displayed individually and all at the same time?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
In your query, define a column as:

[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]

and include a OR condition of IS Null

This:

IIf(IsNull([forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]),"*",[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop])

 Then is not needed.  Just look at the form control for the criteria check on the number.

Jim.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
and just to be clear, the SQL will end up as:

<some field> = [forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]

Or

[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]  Is Null

The later resulting in all records.

Jim.
toalexsandr

Author

Commented:
Thanks for quick response, unfortunately its not working...using expression builder...

[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]  Or Is Null

this returns just 2 blank rows and when selecting individual annexes it adds the blank rows....

My combo box has the following list values, " ", 0600000, 1502000, 1602000, 1700000 and 1800000. Maybe instead of blank I need to have something else?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
toalexsandr

Author

Commented:
Maybe this will be easier with VBA code?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
Check your syntax...

Jim's Post:
[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop] Or [forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]  Is Null

Your post:
[forms]![fr_DailyWOMYPInspectionSelection]![cmb_AnnexDrop]  Or Is Null

Try it exactly the way Jim posted it...
toalexsandr

Author

Commented:
Thank you for your help. It worked perfectly. The problem was that I was adding empty line space in my combo box and it was not searching properly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial