So as in the example above, If I click Campbellton, it will give me records for the Campbellton Center
If I click Bathurst, it will give me records for Bathurst center.

However, I made a category named "All" - If I choose "All" I would like to be able to query all results from Campbellton and Bathurst together. How would I write that to work in the criteria box?

Try IIF Function to check if the combo is null and change the crtieria

Actually this is probably easier than IIF.
=[Forms]![ServiceStaffF]![CenterCombo] Or [Forms]![ServiceStaffF]![CenterCombo] Is Null

This is not working.

If I input =[Forms]![ServiceStatF]![CenterCombo] Or [Forms]![ServiceStatF]![CenterCombo] Is Null in the criteria box, it will create a new section in the query. But when go back to my combo box and select "All", there is no records showing up, that is because the query is still trying to find a criteria because there is something written in the criteria box.

'Is Null' in itself is a criteria input I believe. If you write "Is Null" it's not just going to leave the criteria box "blank". What I would like the query to do is: if I do not specify any of the two centers, it thus removes the criteria as if it was blank, in this manner it will give me all the records. I'm not sure even the Iff function can work because in the false statement of iff, it would still be considered a criteria input in itself.

I tried to input this out of two ways as pictured above, but when I save the query and select "All" in my combo box, no record is showing: Picture below.

Also, when I get back in the query after trying this, it removed the statement and created another field (below)

Oh you have an actual choice of All, what i sent will only work if you make it blank, youll need to use iif then. If you dont get it before i get back to a pc ill write up something.

You can use this criteria with an "All" option
Like "*" & IIf([Forms]![ServiceStaffF]![CenterCombo]="All","",[Forms]![ServiceStaffF]![CenterCombo]) & "*"
Then all will return all records with a value in the record. It will not return Nulls, if you need Nulls too you'll have to do more

Alternatively, you could change this to the much more customizable VBA option. Using something similar to the Search Form 2.0 or Access Search Seminar.

Thanks for trying to help, but it still does not work.

From the first picture I posted I had the "All" option shown. ..Also It is "Stat" and not "Staff" (it actually powers a Statistics table, in the footer, the background records are usually hidden the user - I will send a picture of what it actually looks like).

Regardless, I also tried leaving it blank the first time and it didn't work as well (didn't give me all record when I selected the blank option). I've tried all day yesturday, plenty of combinations, this is why I am desperate to find help.

Now, I tried putting in the code you provided---     Like "*" & IIf([Forms]![ServiceStatF]![CenterCombo]="All","",[Forms]![ServiceStatF]![CenterCombo]) & "*"   ---but it still does not show all records when I select "All". I also entered, IIf([Forms]![ServiceStatF]![CenterCombo]="All","",[Forms]![ServiceStatF]![CenterCombo]) to no avail. It shows the record for Campbellton and Bathurst when I click the option, but I don't know how to make it show all records by using the combobox.

For some reason, the empty string "" does not make the query act as if the criteria field is empty since it is populating no records.

See (below), if I leave the criteria empty, then I get all the records, but I won't be able use criteria to get only a specific center to show.

I would move away from the query then and use vba like in those videos i linked. It's far easier to work with.

Campbellton is CenterID=1 (Three Records) and Bathurst is CenterID=2 (Two Records) - in the example above, it shows all 5 records because I did not use any criteria in the query, but I will need to use criteria in order to filter only center #1 or #2 accordingly. Thanks for the searchform 2.0 suggestion I will check it out whenever I can. However, if someone can find a solution to my dilemma, I would be grateful.

I will consider your advise to move away from the query. I'll check out the video link you sent later and let you know if anything worked out. Thanks

Hey Man,

I actually got it working with ---  Like "*" & IIf([Forms]![ServiceStatF]![CenterCombo]="All","",[Forms]![ServiceStatF]![CenterCombo]) & "*"   --- the problem was that I still had a duplicate field as a "Or" condition at the end of the query that I didn't notice (Access generated it there in prior attempts). I removed it and it worked like a charm. Thanks a lot, I be sending you a little tip of course.

Thank you, glad to hear its working

I was gonna say I covered this exact topic in a lesson but I'm on my phone now and can't remember which one it was. Glad you figured it out.