query to find records with incorect or missing entries

[jerryreeve]

in access 2000
the table has a field for initials that is 3 text char field, I want a query that will sort and display the records that are either null (is null) or not any of a list of valid initials.

I have tried in design to do this by setting the criteria for is null or "aaa" or "bbb" or "ccc" and this found the null records but not the record that was not aaa bbb or ccc. I also have a table with a field of valid initials but am not able to get the query able to look at this table.

any suggestions?

----------------------------
JerryReeve
Communications Systems Int'l
com-sys.com

 

[PWise]

try:

select * 
from tablename
left join initialsTable
on tablename.initialField =initialsTable.initialField 
where initialsTable.initialField  is null

 

[MakeItSo]

hi Jerry,

if you already have a lookup table for your initials, you should be able to query like this:

SELECT * FROM [your table] WHERE (([Initial] IS NOT NULL) AND INITIAL IN
(SELECT [INITIAL] FROM [Lookuptable]));

;-)

Cheers,
Andy

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Arglll :

IS NULL of course, not IS NOT NULL


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jerryreeve]

I am trying this at this time. will advise if I get it going.

----------------------------
JerryReeve
Communications Systems Int'l
com-sys.com

 

[PHV]

set the criteria for Is Null OR not In ("aaa","bbb","ccc")

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