double negative?

[alex]

Experts,

Can someone please tell me the proper SQL code to get the following
result:

All records from this table (EMPLOYEE) where (status is not 9 or 2 AND
hire_dt is >= 01/17/05).

I've written some code (and used the design feature) numerous times
and can only come up with one record (2nd from the last).

Select *

From employee

Where (status not in (2,9) and hire_dt >=#01/17/05)

I basically want all the records (employee_id 12-27 and 29) except for
the two that match BOTH criterian above.

Thanks.

employee_id status hire_dt
0012 9 01/01/2005
0013 5 01/02/2005
0014 3 01/03/2005
0015 2 01/04/2005
0016 5 01/05/2005
0017 4 01/06/2005
0018 3 01/07/2005
0019 2 01/08/2005
0020 8 01/09/2005
0021 3 01/10/2005
0022 6 01/11/2005
0023 4 01/12/2005
0024 1 01/13/2005
0025 2 01/14/2005
0026 4 01/15/2005
0027 7 01/16/2005
0028 9 01/17/2005
0029 0 01/18/2005
0030 2 01/19/2005

 

[alex]

I'm sorry to be confusing.

I'm looking for all the records except employee_id 28 and 30.

I want to write a query that gives me all the records where status is
not 9 or 2 AND
hire_dt is >= 01/17/05...meaning both pieces of criterian need to be
true.

 

[Douglas J. Steele]

But only 3 of your entries have a hire_dt value that's greater than or equal
to 17 Jan, 2005!

Looking at your data, employee 29 is the only one that matches your
criteria!

Do you mean you mean you want status not 9 or 2 AND hire_dt not >= 01/17/05?

Select *
From employee
Where (status not in (2,9) and hire_dt <#01/17/05#)

 

[Bill Mosca, MS Access MVP]

Alex

Try this one:
where (status not in(9,2)) AND
hire_dt is >= 01/17/05.

 

[alex]

Thanks for your response. I seem to be running into the same problem
no matter how i run each query.

As soon as I say 'status not in (2,9)' Access omits any status of 2 or
9. Even though, I think, since you place ( ) around the entire
statement, both must be true, i.e., not in status 2 or 9 (in (2,9)) AND
the hiring date is >=01/17/05.

Access is kicking any 2 or 9 despite the hiring date, which is the crux
of the query.

 

[Douglas J. Steele]

Any time you use AND, both must be true.

For your given data, the hire_date is < 01/17/2005 for employees 12-27,
therefore they'll never pass your criteria, regardless of what status they
have.