Query criteria help

[AC5FF]

This one should be easy; but my brain ain't catching it ....
I want to be able to choose to run a report based upon shop selection; which is selected via a drop-down on a form. That part is working fine. However I would like to run this query so that both shops are included in the results.

The drop down lists; Shop1, Shop2, Shop3, All
The query (design view -- i'm still no good at SQL) has a "Where" field that uses an IIF statement:

IIf([forms]![report].[combo41]="All", ??????? ,[forms]![report].[combo41])

The ??????? here is what is confusing me. Is there anything (wildcard?) that I can put into this IIF statement that will select ALL shops? (basically ignore the WHERE clause)??

I have gone ahead and copied the SQL here.. hopefully it'll show more what I'm looking for:

INSERT INTO [Report Card] ( Product, Part, Repaired )
SELECT product.Abbr, product.part, Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(forms![report].combo31),Month(forms![report].combo31),1) And DateSerial(Year(forms![report].combo31),Month(Forms![report].combo31)+1,0),1,0)) AS Repaired
FROM Area INNER JOIN (lru INNER JOIN [Work] ON product.ID = Work.[product ID]) ON Area.ID = lru.Team
WHERE (((lru.quotas)=True))
GROUP BY product.Abbr, product.part, Area.name
HAVING (((Area.name)=IIf([forms]![report].[combo41]="All","***",[forms]![report].[combo41])));

It's that "HAVING" line i guess that's tripping me up. BTW: I used "***" here because the field is only 3char long.

 

[AC5FF]

Pat;
Don't believe that'll cover what I need. There is no "ALL" option in the AREA.NAME field.

Here is what I tried this morning. I got rid of the IIF statement and replaced it with what I wanted to have as part of the IIF statement's true criteria: "Shop1 or Shop2 or Shop3" When I did this, the query ran fine.
So, I re-insert the IIF statement as written above and replaced the ???? with "Shop1 or Shop2 or Shop3". Access changed it to (area.name)="shop1" or (area.name)="shop2"...etc
Now when I run the query I get an error "Data Type MisMatch in Criteria Expression"

Reading through my multiple books here I'm getting even more lost LOL.
I have seen an option for "ALL" being a conditional criteria, that didn't work. I've tried using IN ("X","XX") with no luck.
I've tried wildcards, some books say ? is a wildcard, some say % some say * and some even say _ All are either single or multiple character wild cards. I've tried em all w/out success.

I just need to select "ALL" records if "ALL" is what is selected in the combo41 box! Frustrating.....

 

[AC5FF]

Got it to work...
here's a copy of the whole coding:

 LRU, NSN, Repaired )
SELECT lru.Abbr, lru.NSN, Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(forms![enterprise report card].combo31),Month(forms![enterprise report card].combo31),1) And DateSerial(Year(forms![enterprise report card].combo31),Month(Forms![enterprise report card].combo31)+1,0),1,0)) AS Repaired
FROM Area INNER JOIN (lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID]) ON Area.ID = lru.Team
WHERE (((Area.name)=IIf([forms]![enterprise report card].[combo41]="All",[Area].[name],[forms]![enterprise report card].[combo41])) AND ((lru.Enterprise)=True))
GROUP BY lru.Abbr, lru.NSN;

 

[AC5FF]

Pat;
I never did get a chance to try this; I will give it a shot tomorrow once I get back to work...
but I may not be understanding the whole code. You said to look at the whole where statement. Again, maybe missing something and I will be interested to give this a try.

Because...

Now that this is working there's a third step I need to work into the query. As it runs now, I get 30 records if I choose "DAV" and 43 if I choose "REW" and 73 if choosing "ALL". My 4th selection on the combo41 field is "161Units". In both shops, a few of the units are flagged (LRU.ANQ161) as True. The 4th version of the report needs to only list these items (5 from DAV and 30 from REW).

I thought once I could get the "ALL" to work it would be an easy fix. But when I throw a new condition on the "WHERE" it throws everything out of wack. Now, I'm mainly working in Design View. I added another column, ANQ161, chose Where and tried another IIF condition statement. (IIF combo41=161Units, true, false) I also added this same condition on the Area.name on the next line down.
This might be the wrong way to go about this.... Heck, probably is.. I read a little today in one of the books for using 'sub queries' when writing SQL... I'm starting to think that I might need something like that... Dunno...

Any other ideas??

Thx!

 

[AC5FF]

UPDATE:
Worked this a while this morning and got everything to work as required!
Thanks again Pat for the help!!