Why won't my "Or" work?

[edowling]

This may be a simple question but I've been hitting my head against the computer screen all day trying to get my query to work.

So, try this...

I have an IIF statement that searches through all the records in my database and keeps a running sum of the instances that a certain value is found in a field (Primary_Procedure). An example is as follows:

SELECT Sum(IIf(([EncounterTable].[Primary_Procedure])="99384" or "99385",1,0)) AS Immunize
FROM EncounterTable;

What is suppose to happen is the query keep a running sum of all the instances that the Primary_Procedure variable = 99384 or 99385 (these are text not numbers because there are some codes in this field that have numbers and letters). What is actually happening is when I just have one criteria specified:

SELECT Sum(IIf(([EncounterTable].[Primary_Procedure])="99384",1,0))

I get the number 3 back, which is correct.

When I try to look for more than one criteria at a time (99384 AND 99385), I get back 19, which is the total number of records in my database (so far). I should really be getting back 5, the total number of records with 99384 or 99385 in the specified field.

What am I doing wrong? How can I search for multiple criteria in this query?

Thanks,

Lis

 

[LarryDeLaruelle]

Lis:

You need to specifiy the field to compare after the OR:

SELECT Sum(IIf(([EncounterTable].[Primary_Procedure])="99384" or [EncounterTable].[Primary_Procedure] = "99385",1,0)) AS Immunize
FROM EncounterTable;

Try that. Should give you the result you're looking for.
Larry De Laruelle
larry1de@yahoo.com

 

[edowling]

Larry:

Thanks, that did the trick! I knew it was something simple. I guess I just thought there was a "shorter" way of writing it.

Thanks,

Lis

 

[LarryDeLaruelle]

Lis:

You're welcome.

I've tried to do the same thing.

If it's true that we learn from our mistakes, then I should be a much more knowledgeable than I am since I make so many of them.
Larry De Laruelle
larry1de@yahoo.com