SQL: "AND" & "OR" problems... Query will not work 2

[kmkland]

I have the following query:

SELECT tblEmpInfo.[EmpFile#], [tblEmpInfo]![FName] & ' ' & [tblEmpInfo]![MName] & ' ' & [tblEmpInfo]![LName] AS EmpName, tblAbsences.Date_of_Absence, tblAbsences.Code, tblReasonsForAbsences.Reasons_for_Absences, tblAbsences.Action_Taken, tblAbsences.Date_of_Action, tblAbsences.Hours_of_Leave
FROM (tblEmpInfo INNER JOIN tblHireDates ON tblEmpInfo.[EmpFile#] = tblHireDates.[EmpFile#]) INNER JOIN (tblAbsences INNER JOIN tblReasonsForAbsences ON tblAbsences.Code = tblReasonsForAbsences.Code) ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE (((tblEmpInfo.[EmpFile#])="9183") AND ((tblAbsences.Date_of_Absence)>=Date()-365) AND ((tblAbsences.Code)="21") AND ((tblHireDates.Date_of_Separation) Is Null)) OR (((tblAbsences.Code)="22")) OR (((tblAbsences.Code)="23"));

which is supposed to only show EmpFile# 9183 in the results but is showing all EmpFile#'s with Codes 21, 22, and 23.

I can't quite figure out what is wrong with this statement!!!
Any suggestions?????????

Any and all help is greatly appreciated...always!

Rgds,
KMK

 

[jebry]

Hi!

Try this:

SELECT tblEmpInfo.[EmpFile#], [tblEmpInfo]![FName] & ' ' & [tblEmpInfo]![MName] & ' ' & [tblEmpInfo]![LName] AS EmpName, tblAbsences.Date_of_Absence, tblAbsences.Code, tblReasonsForAbsences.Reasons_for_Absences, tblAbsences.Action_Taken, tblAbsences.Date_of_Action, tblAbsences.Hours_of_Leave
FROM (tblEmpInfo INNER JOIN tblHireDates ON tblEmpInfo.[EmpFile#] = tblHireDates.[EmpFile#]) INNER JOIN (tblAbsences INNER JOIN tblReasonsForAbsences ON tblAbsences.Code = tblReasonsForAbsences.Code) ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE tblEmpInfo.[EmpFile#]="9183" AND tblAbsences.Date_of_Absence>=Date()-365 AND tblAbsences.Code="21" AND tblHireDates.Date_of_Separation Is Null AND (tblAbsences.Code="22" OR tblAbsences.Code="23");

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[kmkland]

Jeff,
I received no results from that statement....
Hmmm....
Rgds,
Kim

 

[kmkland]

Jeff,
I figured it out....

SELECT tblEmpInfo.[EmpFile#], [tblEmpInfo]![FName] & ' ' & [tblEmpInfo]![MName] & ' ' & [tblEmpInfo]![LName] AS EmpName, tblAbsences.Date_of_Absence, tblAbsences.Code, tblReasonsForAbsences.Reasons_for_Absences, tblAbsences.Action_Taken, tblAbsences.Date_of_Action, tblAbsences.Hours_of_Leave
FROM (tblEmpInfo INNER JOIN tblHireDates ON tblEmpInfo.[EmpFile#] = tblHireDates.[EmpFile#]) INNER JOIN (tblAbsences INNER JOIN tblReasonsForAbsences ON tblAbsences.Code = tblReasonsForAbsences.Code) ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE (((tblEmpInfo.[EmpFile#])="9183") AND ((tblAbsences.Date_of_Absence)>=Date()-365) AND ((tblAbsences.Code)="21" Or ((tblAbsences.Code)="22" Or (tblAbsences.Code)="23")) AND ((tblHireDates.Date_of_Separation) Is Null));

This worked.
Thanks for your help!!!
Kim

 

[PHV]

A simplified WHERE clause:
WHERE tblEmpInfo.[EmpFile#]="9183" AND tblAbsences.Date_of_Absence>=Date()-365 AND tblAbsences.Code In ("21", "22", "23") AND tblHireDates.Date_of_Separation Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886