Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ABS Problem in a Select Query

Status
Not open for further replies.

quest4

Technical User
Aug 27, 2001
735
US
I have a select query which is being used to identify employees who are on vacation or jury duty for more than 3 days in a week. The query can identify and list the employees who are off for the entire week, 5 days, but is not picking up the employees who are off for 3 or 4 days. Here is a copy of the query:
SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence AS Att
WHERE (Att.DateWeekStarting
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd("ww",-14,Date())))
And Abs((Att.WorkDay1Reason="JurD-A" Or Att.WorkDay1Reason="VacD-A")
+(Att.WorkDay2Reason="JurD-A"
Or Att.WorkDay2Reason="VacD-A")
+(Att.WorkDay3Reason="JurD-A"
Or Att.WorkDay3Reason="VacD-A")
+(Att.WorkDay4Reason="JurD-A"
Or Att.WorkDay4Reason="VacD-A")
+(Att.WorkDay5Reason="JurD-A"
Or Att.WorkDay5Reason="VacD-A"))>=3
ORDER BY Att.EmployeeNumber;
Any help in straightening out this query will be greatly appreciated. Thank you very much for all of the assistance.
 
I have found the errors of my ways. For anyone interested in ABS, you must use an Nz with each of the parameters or reasons.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top