SQL 'Where criteria issue' 2

[LuckyDanger]

The main aim of the SQL query is to show only records with 'A0' or 'A1' in a field and also only show those records in another field with 't1' or 't2'

Not all appropriate records are returned with this query i.e. expected results show records missing.


Here is my sql query:

SELECT tab1.accnt_code, tab1.Period, tab1.trans_date, tab1_amount, tab1.anal_t0, tab1.anal_t1, tab1.anal_t2, tab1.anal_t3, tab1.anal_t4, tab1.anal_t5
FROM sindb.dbo.tab1 tab1
WHERE (tab1.anal_t2=’A1’) OR
(tab1.anal_t2=’A0’) AND
(tab1.accnt_code=’T1’) OR
(tab1.accnt_code=’T2’)
ORDER BY tab1.accn_code, tab1.anal_t1

 

[dhookom]

I can't see anything wrong with the results. I assume you want to AND and not OR between the Anal_T2 and Accont_Code where conditions.

SELECT tab1.accnt_code, tab1.Period, tab1.trans_date, tab1_amount, tab1.anal_t0, tab1.anal_t1, tab1.anal_t2, tab1.anal_t3, tab1.anal_t4, tab1.anal_t5
FROM sindb.dbo.tab1 tab1
WHERE tab1.anal_t2 IN ('A1','A0') AND tab1.accnt_code IN ('T1','T2')
ORDER BY tab1.accn_code, tab1.anal_t1

It looks like you data might reside outside Access. Some databases are case-sensitive.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

You may try this WHERE clause:

WHERE tab1.anal_t2 IN ('A0','A1) AND tab1.accnt_code IN ('T1','T2')

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

 

[Golom]

AND is processed before OR so your SQL will be interpreted as

WHERE (tab1.anal_t2='A1') OR
[red]((tab1.anal_t2='A0') AND
(tab1.accnt_code='T1'))[/red] OR
(tab1.accnt_code='T2')

where the part in [red]red[/red] will be evaluated first and then the OR clauses will be evaluated.

You probably want

WHERE tab1.anal_t2    IN ('A1', 'A0') 

      AND

      tab1.accnt_code IN ('T1', 'T2')

 

[LuckyDanger]

Thanks to both! PHV & Golom

 

[traingamer]

Both of those answers were correct. Another solution would be to use enough parentheses to make your code unambiguous:

WHERE [red][b]([/b][/red] (tab1.anal_t2='A1') OR tab1.anal_t2='A0') [red][b])[/b][/red] AND
[red][b]([/b][/red] (tab1.accnt_code='T1') OR (tab1.accnt_code='T2')[red][b])[/b][/red]

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard