Filtering a query via selection in cbo and if no selection filter as if blank (1 Viewer)

[chrisjames25]

Hi

I have a main form which has a subform. As i choose from combo boxes in main form the subform is filtered to less inputs based on my selection.

The criteria i am using in the query design is "Like "*" & [forms]![Frm_genusAdd]![Cbo_Tier1]"

Works great most the time, however the combobox first column is an unique id. The issue is if I choose something with unique id 5 it will also keep in the filter unique id 15 also and 25,35,45 etc.

I tried removing the "like * &" at start of criteria but then I get nothing appear in the subform. I then tried adding an Or is null but again form populates no data.

My workaround is to have afterupdate event of cbo to pass string from the second column in the cbo to a txt box and also add a criteria for that too - ""Like "*" & [forms]![Frm_genusAdd]![Txt_Tier1]". This works but i'm assuming a better way to do this.

Apologies if explained really badly.

 

[pbaldy]

Try

The Access Web - Welcome

 

[Pat Hartman]

Since the combo provides a complete ID, there is no reason to use Like.

 

[chrisjames25]

Since the combo provides a complete ID, there is no reason to use Like.

When i get rid of the like statement then nothing appears in the subform. I think it is cos the cbo is blank at that point. What i dont know how to do is do a query design to say use cbo ID number selected but if cbo is blank use no ID

 

[Pat Hartman]

The criteria can be:

Where (somefield = [forms]![Frm_genusAdd]![Cbo_Tier1] OR [forms]![Frm_genusAdd]![Cbo_Tier1] Is Null)

When noting is selected, you get everything. When something is selected, you get just that.

 

[pbaldy]

Which is what I suggested in the first place.

 

[Pat Hartman]

Which is what I suggested in the first place.

Perhaps, but Chris would have needed to find the right article. I scanned the titles and couldn't pick out which one to look at.

 

[pbaldy]

Crap, I copied the index instead of the article. Meant to do the first one:

Queries: Use a parameter to return all records if Null

 

[Gasman]

Was actually the very first link on that page?

 

[pbaldy]

Was actually the very first link on that page?

Yes, but I goofed up and didn't drill into it before copying the link.

 

[Pat Hartman]

And I'm blind

 

[GPGeorge]

When i get rid of the like statement then nothing appears in the subform. I think it is cos the cbo is blank at that point. What i dont know how to do is do a query design to say use cbo ID number selected but if cbo is blank use no ID

I have a demo on my website that does this. It uses the Iif() expression and Tempvars. See if it can give you a guide to implement something similar in your situation. It is based on the exact match, but could be modified to use Like.