Eliminate rows 1

[regava]

I have the following query:

SELECT "BRIEFED" as Type, T1.Name
iif((T2.a < Date and T2.b = "N" and T2.C <> "Y")1,0) as Pet
iif((T2.a < Date and T2.b = "N" and T2.C <> "Y" and T1.D = "Y"),1,0) as Cas
T1.Dp, T2.a, T2.b, T2.C, T1.D
FROM tblTrack as T1 INNER JOIN tblCdr as T2 ON T1.No = T2.No
WHERE NOT (Pet = 0 and Cas =0);

I want to select only those rows in which Pet<>0 and Cas<>0
When I execute the query I get the message "Enter Parameter Value" for Pet and Cas. If I do not have the WHERE the query executes correctly. WHAT AM I DOING WRONG? Help is appreciated.

 

[PHV]

SELECT ...
WHERE T2.a < Date() AND T2.b = 'N' AND T2.C <> 'Y'

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

 

[randy700]

Perhaps...

WHERE NOT ([b][COLOR=red][[/color][/b]Pet[b][COLOR=red]][/color][/b] = 0 and [b][COLOR=red][[/color][/b]Cas[b][COLOR=red]][/color][/b] =0)

Randy

 

[regava]

Randy,
I tried your suggestion and got the same result. Thank you

PHV,
Thank you for your wonderful solution. It did what I expected.

 

[lespaul]

you can't use an alias in the same query....so you have to replace PET and CAS in the where clause with the formula you used to get PET and CAS.....

Leslie

Have you met Hardy Heron?

 

[regava]

Leslie,
I like your solution better. It works perfect and makes it easy to understand. Thanks a million, it is appreciated.