Combo box selection for store number or 'ALL'

[snipe]

Hey guys,



I am stumped on something that I feel should be very simple.

I have a form that runs a report based off of the store number that is in a combo box. The numbers are 1 through 8. I wanted to add another option, 'ALL', that would run the report for all of the stores.

In the query, I used the following criteria:

IIf([Forms]![Switchboard]![Store] Like "ALL",Between "01" And "21",[Forms]![Switchboard]![Store])

I am getting the "too complex" error. What is the proper way to accomplish this?

Appreciate the help.

Edit: we have stores 1-8 and a store 21 is our outlet, hence why the query goes through 21.

[ItsMe]

Maybe the between expression does not like comparing text.

Why are you using an instant if?

Can't you simply pass the combo value in the where criteria of a Docmd.OpenReport. Maybe use a "Select All" radio button for the user. I imagine "Select All" would not require criteria for that field (store).

[orange]

You could use a value list in the combo source "All","1","2"....
I've seen a SELECT with a UNION to make any list you need.

[John_G]

I don't think the syntax of the IIF is correct. The function returns a value, which in ths case you want to be the string
Between "01" and "21" (I think!). So, your IIF has to be modified to return a string. Your expression Between "01" And "21" is not valid in this context in the IIF. Change it to "Between ""01"" And ""21""" so that the actual string will be returned.

Now, is the field you are using the criteria for numeric (a store number), or text? And what does the combo box return - number or text? These are important distinctions, because "08" (string) is not the same as 8 (number). MS Access can sometimes figure it out, but you can't count on it.

Can you provide more info, please.

John

[snipe]

Can't you simply pass the combo value in the where criteria of a Docmd.OpenReport. Maybe use a "Select All" radio button for the user. I imagine "Select All" would not require criteria for that field (store).

I am trying to limit the data in the queries that run to reduce processing time. With all stores, its about a 15 minute process. For 1 store, its about 5 minutes.

You could use a value list in the combo source "All","1","2"....
I've seen a SELECT with a UNION to make any list you need.

The combo box is a value list just like you said. You lost me with the UNION. Never touched that before.

I don't think the syntax of the IIF is correct. The function returns a value, which in ths case you want to be the string
Between "01" and "21" (I think!). So, your IIF has to be modified to return a string. Your expression Between "01" And "21" is not valid in this context in the IIF. Change it to "Between ""01"" And ""21""" so that the actual string will be returned.

Now, is the field you are using the criteria for numeric (a store number), or text? And what does the combo box return - number or text? These are important distinctions, because "08" (string) is not the same as 8 (number). MS Access can sometimes figure it out, but you can't count on it.

Can you provide more info, please.

John

John I think you might have hit the nail on the head. Adding the extra quotes has eliminated the error. When I select a single store from the combo box, the query runs great. If I select ALL, I get no data. I feel like this is progress.

The store number field is text, as is the combo box (blank format property).

[snipe]

Ok the plot thickens:

I changed the IIf to be:

IIf([Forms]![Switchboard]![Store] Like "ALL","02",[Forms]![Switchboard]![Store])

When I select ALL from the combo, the query only pulls data from store 2.

I removed the IIF completely and just used Between "01" and "21" as the criteria, and it worked great.

Why can't I combine the two??

[John_G]

"When I select ALL from the combo, the query only pulls data from store 2."

Yes, because your IIF returns the string "02" when you select "All" from the combo box and uses that as the criteria. That's good (sort of), because you know the combo box and the IIF are working.

Try this:

Iif([Forms]![Switchboard]![Store] Like "ALL","",[Forms]![Switchboard]![Store])

The IIF will return a zero-length string - in other words, no limiting criteria - if you select "ALL"

John

[snipe]

That's why I changed the IIF to "02", to test to make sure it was working.

No go on the zero-length string. Putting "" as the criteria brings back no data.

[John_G]

I had a hunch it might do that - a zero-length string is valid criteria, and of course it didnt find any.

Try this:

Iif([Forms]![Switchboard]![Store] Like "ALL",Null,[Forms]![Switchboard]![Store])

with the Null not in quotation marks, so that there really are no criteria if "All" is chosen.

John

[snipe]

No go again.

Testing it failed as well. Access changes it to Is Null automatically, and obviously, none of the stores are null.

[orange]

Did you try a Value list?

[snipe]

Did you try a Value list?

My combo box on the form is a value list. If you are referring to anything else, you've lost me. Explain?

[June7]

Try:

LIKE IIf([Forms]![Switchboard]![Store] = "ALL", "*", [Forms]![Switchboard]![Store])

Works for me.

[John_G]

Hi -

A bit of trial and mostly error in MS Access has shown what the problem is. It has to do when the IIF is evaluated. The IIF is passed through to the SQL of the query, and is evaluated there, when the query is run.

The syntax in the query WHERE are different for Like... and = ;

One is Where fieldname = value and the other is WHERE fieldname like "a*"

Access doesn't see which is which, and so you end up with something like :

Game_Summary.Team_Code= IIf(1=2,"TOR",Game_Summary.Team_Code Like "*")

and while it does not seem to give an error, it sure doesn't work.

Time to go home - I'll give it some thought to see if I can come up with anything.

One thing you might try is to create and run the query SQL right in the form (as a report wherecondition), rather than using a stored query.

John

[snipe]

Try:

LIKE IIf([Forms]![Switchboard]![Store] = "ALL", "*", [Forms]![Switchboard]![Store])

Works for me.

That is what I have currently, and it works. But, we use a bunch of different store number for other things: corporate returns, exchanges, testing, floor model ordering, etc. We have 9 physical stores, yet 22 store numbers. In the grand scheme of things, this only 2-3% different. I am making do with it for now while I am programming. But when I roll this out, I need "ALL" to only be stores 1-8 and 21.