Hello! I have the following query:

SELECT DISTINCT tblEmployees.ClockID, [Emplastname] & ", " & [EmpFirstName] & " " & [MI] AS Employee, tblEmployeesAbsences.EmpID, tblEmployeesAbsences.ABPoints
FROM tblEmployees INNER JOIN tblEmployeesAbsences ON tblEmployees.EmpID = tblEmployeesAbsences.EmpID
WHERE (((tblEmployeesAbsences.EmpID) Not In (Select Distinct EmpID From tblEmployeesAbsences Where (tblEmployeesAbsences.LookupID = 198 or tblEmployeesAbsences.LookupID = 199 or tblEmployeesAbsences.LookupID = 200 or tblEmployeesAbsences.LookupID = 201 or tblEmployeesAbsences.LookupID = 204 or tblEmployeesAbsences.LookupID = 205 or tblEmployeesAbsences.LookupID = 206 or tblEmployeesAbsences.LookupID = 207 or tblEmployeesAbsences.LookupID = 208 or tblEmployeesAbsences.LookupID = 209 or tblEmployeesAbsences.LookupID = 212 or tblEmployeesAbsences.LookupID = 213 or tblEmployeesAbsences.LookupID = 214 or tblEmployeesAbsences.LookupID = 215 or tblEmployeesAbsences.LookupID = 216 or tblEmployeesAbsences.LookupID = 217 or tblEmployeesAbsences.LookupID = 218 or tblEmployeesAbsences.LookupID = 378 or tblEmployeesAbsences.LookupID = 379 or tblEmployeesAbsences.LookupID = 378 or tblEmployeesAbsences.LookupID = 380 or tblEmployeesAbsences.LookupID = 381 or tblEmployeesAbsences.LookupID = 453))) AND ((tblEmployees.Active)=Yes) AND ((tblEmployees.PPB)=Yes) AND ((tblEmployees.EUnion)=Yes) AND ((tblEmployeesAbsences.ABDate) Between [Enter Start date Ex:1/1] And [Enter End Date Ex: 2/28]))
ORDER BY [Emplastname] & ", " & [EmpFirstName] & " " & [MI];

This is not returning the correct information. If an employee as a disqualifying absence they are eliminated from the results which is correct, but I need it to be date specific. In other words, if they have no disqualifying absences during a date range between 1/1/05 and 2/28/05, they should show in the results, but what I'm finding is that if that same employee has a disqualifying absence BEFORE or AFTER the above date range, they are being eliminated from the results and shouldn't be.

Can anyone tell me what I'm doing wrong or how I can get the correct results?

You are probably doing this but I wanted to make sure you are enclosing the dates in your SQL with #

Actually, I'm not using # between dates. The reason is that I want to be able to use this query for any date range, so I'm using Between [] and [] to change the date range.

I did try to then "hard code" the dates using the following:

Between #01/01/2005# And #02/28/2005#

But the query is still eliminating employees who have disqualifying absences AFTER 02/28/05.

What I need the query to do is to return a list of all employees that did not have a disqualifying absence between a date range keyed in by the user. I'm not concerned about disqualifying absences BEFORE or AFTER the date range.

What can I do to get it to do this?

Try adding a date criteria to the subquery.
SELECT DISTINCT tblEmployees.ClockID, [Emplastname] & ", " & [EmpFirstName] & " " & [MI] AS Employee, tblEmployeesAbsences.EmpID, tblEmployeesAbsences.ABPoints
FROM tblEmployees INNER JOIN tblEmployeesAbsences ON tblEmployees.EmpID = tblEmployeesAbsences.EmpID
WHERE (((tblEmployeesAbsences.EmpID) Not In (Select Distinct EmpID From tblEmployeesAbsences Where (tblEmployeesAbsences.LookupID = 198 or tblEmployeesAbsences.LookupID = 199 or tblEmployeesAbsences.LookupID = 200 or tblEmployeesAbsences.LookupID = 201 or tblEmployeesAbsences.LookupID = 204 or tblEmployeesAbsences.LookupID = 205 or tblEmployeesAbsences.LookupID = 206 or tblEmployeesAbsences.LookupID = 207 or tblEmployeesAbsences.LookupID = 208 or tblEmployeesAbsences.LookupID = 209 or tblEmployeesAbsences.LookupID = 212 or tblEmployeesAbsences.LookupID = 213 or tblEmployeesAbsences.LookupID = 214 or tblEmployeesAbsences.LookupID = 215 or tblEmployeesAbsences.LookupID = 216 or tblEmployeesAbsences.LookupID = 217 or tblEmployeesAbsences.LookupID = 218 or tblEmployeesAbsences.LookupID = 378 or tblEmployeesAbsences.LookupID = 379 or tblEmployeesAbsences.LookupID = 378 or tblEmployeesAbsences.LookupID = 380 or tblEmployeesAbsences.LookupID = 381 or tblEmployeesAbsences.LookupID = 453)) AND ((tblEmployeesAbsences.ABDate) Between [Enter Start date Ex:1/1] And [Enter End Date Ex: 2/28])) AND ((tblEmployees.Active)=Yes) AND ((tblEmployees.PPB)=Yes) AND ((tblEmployees.EUnion)=Yes) AND ((tblEmployeesAbsences.ABDate) Between [Enter Start date Ex:1/1] And [Enter End Date Ex: 2/28]))
ORDER BY [Emplastname] & ", " & [EmpFirstName] & " " & [MI];

I guess I'm not following exactly what you mean.

I copied your code into my query and it still returns the same results. There is one employee (that I'm aware of) that has no disqualifying absences during the date range of 1/1/05 and 2/28/05, so he should appear in the results, but he doesn't because he has a disqualifying absence on 3/7/05. I've checked and double checked his absences and he only has one absence during this date range which is not a disqualifying one, so the query should return him in the results.

What am I doing wrong?

I'm sorry to be such a bother, but I've been going around in circles. Could someone please help me?

Try this, if this doesn't work, post a sample of your db with dummy data.

I'm attaching a strip-down and scrambled database. The qryFreeFloatsEligiblityUnionDates query was set up to get a list of those employees that would be considered eligible for a free float (free day off with pay). They have to be Union Employees, Active, Subject to points and Passed their probation period. The query returns the entire list of absences during the date range called for. Then I made another query based on the above query to eliminate those absences which are considered "bad" or disqualifying absences. Technically, the only acceptable absences for free day off awards are Vacations and Free Floats.

No matter how I have done a query/queries, the query gives me the wrong results. There are some employees that have no "bad" absences during the period of 1/1 through 2/28, and therefore should get a free day, but these employees have "bad" absences before 1/1 or after 2/28 and the query is therefore elminating them based on the Absence type (LookupID). One employee in particular is Steven McHenry (name scrambled). He should show on my results because he is eligible, but he doesn't because of a disqualifying absence on 3/7.

Another snafu in this is that the employee cannot have any points prior to the date range or during the date range, but after is okay. I haven't even attempted this part yet as I can't get the correct results.

In a perfect world, I need a query that gives me a list of employees that are eligible for a free bonus day that will eventually run an append query giving those employees a free day off award. This will be done quarterly.