IIf statement to show all results

[nigelbloomy]

I have a query linked to a combo box on a form. If the user selects "All Cost Centers" from the combo box then I want the query to show all of the cost centers. If they select a specific cost center then I want the query to show that specific cost center. Right now my criteria code looks like this:

IIf([Forms]![Update Form]![cmdCostCenter]="All Cost Centers",Like "*",[Forms]![Update Form]![cmdCostCenter])

The code works great for selecting specific cost centers. The problem is the "All Cost Centers" functionality. The query returns nothing. I feel like I am missing something very simple.

[JoeM]

Are you sure you are referencing the correct field name?
If you follow standard naming convention, "cmdCostCenter" is probably the name of a Command Button, not a Combo Box.

[nigelbloomy]

I did have the combo box named incorrectly. Thanks for catching that. I fixed the combo box name so it is cmbCostCenter. I changed it in my formulas as well, but still no luck.

[JoeM]

Not sure how to get it to work using exactly the methodology you have proposed. What I usually do is instead of having an "ALL" option in my Combo Box, telling them to leave the combo box blank/empty, and use this methodology to return those records (no IIF statement needed): http://www.mrexcel.com/forum/microso...ml#post3397752

[nigelbloomy]

Leaving the box blank was my first solution based on a few google searches. It does work. My problem is that I need this to be as intuitive as possible. For a normal person leaving the box blank isn't the first thought when they want to see all of the charge codes. This is such a simple IIF statement I am not sure why it doesn't work. Each part of the statement seems to work on it's own. If I put a cost center in the place of the "Like "*" section, the statement returns that cost center just like it should:

IIf([Forms]![Update Form]![cmbCostCenter]="All Cost Centers","63xx",[Forms]![Update Form]![cmbCostCenter])

As soon as I put the Like "*" back in there, the statement returns nothing. IIf([Forms]![Update Form]![cmbCostCenter]="All Cost Centers",Like "*",[Forms]![Update Form]![cmbCostCenter]).

Is there another way to tell the criteria to give me everything?

[JoeM]

It probably has to do with the text qualifiers (double-quotes) or the wildcard. Sometimes, it gets confused between what is literal text and what is not.

Personally, I never have my queries call the criteria directly from Form field like that. I use Forms to make the criteria selection like you do, but instead of having my query call the values of fields from the Form, I instead have VBA code that builds the SQL code of the query on-the-fly, then applies that SQL code to a pre-defined query, and then opens that query. So all you have to do is enter your criteria and click the Command Button to run it.