Selective Criteria in a Query

[AC5FF]

I'm just not sure why this is giving me so many problems. I would think it would be quite easy!

I am running a query from a selection on a form. I want to be able to select data based on what shop works a product.

Here is a copy of the SQL. The problem I have is in the WHERE section:

INSERT INTO [Enterprise Report Card] ( 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])="REW" Or ([Area].[name])="DAV",[forms]![enterprise report card].[combo41])) AND ((lru.Enterprise)=True))
GROUP BY lru.Abbr, lru.NSN;

In the IIF section on the WHERE statement right now I get an error and the code does not run. If I select (from the combo41 box) REW or DAV it works as I need, but if I select "ALL" I cannot get anything back...

Any ideas??

 

[lespaul]

you've got:

IIf([forms]![enterprise report card].[combo41]="All",
([Area].[name])="REW" Or ([Area].[name])="DAV",
[forms]![enterprise report card].[combo41]))

so if Forms!enterprise report card.combo41 = All then

WHERE Area.name = "REW" or Area.name = "DAV"

otherwise

WHERE Area.name = what's in the combobox

is that not what you want it to do?

Leslie

Have you met Hardy Heron?

 

[AC5FF]

Leslie

If I am reading your post correctly, yes... that's what I would like to do.

I'm not sure if this is a suggested fix though. Do I want to put that 'where' statement as the "true" value of the IIF statement?

 

[Golom]

This expression

[Area].[name]="REW" Or [Area].[name]="DAV"

That is selected when your combo is "All" is not a string assignment ... it is a logical expression that will return TRUE or FALSE. Since (I assume) Area.Name is probably a text field, you're where clause is probably being limited to

Area.Name = '0'
OR
Area.Name = '-1'

and likely you don't have any names like that so nothing is returned.

What are you trying to achieve with that clause?

 

[AC5FF]

What I am trying to achieve:
I am using the combo box (combo41) for the user to select the data contained in a report. The user can select shop "DAV", shop "REW", or "ALL" for both shops.

I am using the IIF statement in the WHERE clause to choose what is displayed on the report. If I put just the 'combo41' field in for the criteria it will work if I select either of the two shops, but if I select "ALL" nothing gets returned. (logical since there is no shop "all")

If there's a better way to do this I'm game!! I just haven't found it yet. Been scouring my ACCESS and SQL books here looking for a solution and I've tried TONS of different ways, but nothing works for the "all" selection.

 

[Golom]

Since "Area.Name" will always be equal to itself

INSERT INTO [Enterprise Report Card] ( 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", 
                      [red]Area.Name,[/red]
                      [forms]![enterprise report card].[combo41])) 
  AND lru.Enterprise=True

GROUP BY lru.Abbr, lru.NSN;

 

[PHV]

WHERE (Area.name=[forms]![enterprise report card].[combo41] OR [forms]![enterprise report card].[combo41]='All')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

An alternative is

WHERE (Area.name = [forms]![enterprise report card].[combo41]
   OR [forms]![enterprise report card].[combo41] = "All")
  AND lru.Enterprise=True

 

[AC5FF]

Golam
I am trying to be professional here but this post of yours is making it quite tough.. (Any 'inappropriate' thoughts were directed at myself here!! )

That's IT????? You've got to be kidding me! Just replace the true portion of the IIF statement w/Area.Name? Worked like a CHAMP! WOW! Can't believe it was that easy! Expecially after everything I tried! THANK YOU!!!!!!

PHV:
I'm not 100% sure I am following your solution. I may toss that in to see what happens, but with the query working now I will probably leave it alone!

 

[AC5FF]

Now; thanks to all for getting that portion to work.
However, I have to add one more step to the process. I didn't bring it up earlier because I think I needed to get over this one hurdle before tackling the next.

I have a 4th selection on the combo41 box: 161Boxes
In this selection I need to choose all the records again from both shops, but filter out only those units that are coded "True" in lru.anq161

I thought this would be an easy addition. But apparently it is not. I am working in design view (I'm no good w/SQL) and thought it would be as simple as adding another WHERE condition to the query. That didn't happen. This messed everything up.

Running the query selecting "REW" returns 30 records, running it w/"DAV" returns 23, and running "ALL" returns 53. Now what I need to do is view only 35 of the 53 records that are coded true for lru.anq161

Ideas? or did I mess things up by not bringing all this up at the beginning?

 

[PHV]

Have a look at the IIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AC5FF]

Here is the code that works for selecting "DAV", "REW", or "ALL"

INSERT INTO [Enterprise Report Card] ( 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]

Update:
Worked this a while this morning and was able to get everything to work as required!! THANKS TONS EVERYONE!!

Quick question though:
Golom posted something about area.name being a 0 or -1. Not sure where that came from. One of the things that tripped me up was the ANQ161 field is a checkbox (True/False). When I wrote the IIF statement in the query I was looking for a "True" value. It never worked like that. I remembered this post and tried 1, then 0, then -1. -1 is what got things to work!

So am i seeing this correctly? If I have a checkbox stored in a table, if I want to query by that box I have to use -1 for True/Yes and 0 for False/No?

Off to the races to update the working DB!

 

[Golom]

What I said was that

[Area].[name]="REW" Or [Area].[name]="DAV"

returns a True or False result. That is

If Area.Name is equal to either of those values then the result will be TRUE which equates to a numeric bit value of -1. If area.Name is NOT equal to either of those values then the expression returns FALSE (which is numerically zero.)

Although those equivalences (True = -1; False = 0) are well known, you can get tripped up by the fact that ANY value that is not zero is considered to be True ... even if it's not equal to -1.