Criteria in a query to exclude one record.

[Robin Chapple]

I have a membership database in which the email address is used as the
unique field for reference purposes.

I run a query that seeks to remove email addresses from a mailing list
if they do not appear in the database.

I need one address to be in the mailing list which is not a member
email address.

I have a query which links the email addresses and seeks the "null"
entries in the database and then deletes them from the mailing list.

I have been using this as a criteria:

Is Null And <>"(e-mail address removed)"

to prevent "(e-mail address removed)" being deleted and it has been working
for months. Suddenly there are no records in the query in spite of the
fact that if I reduce the criteria to "Is Null" the are eight
including "(e-mail address removed)".

I am baffled.

Where do I look next?

Thanks,

Robin Chapple

 

[Ken Snell [MVP]]

Post the SQL of the query you've been using so we can see how you've
structured it.

 

[Ed Robichaud]

Pretty sure you want to select/delete records that have no email address OR
where the email is not (e-mail address removed)
-Ed

 

[Robin Chapple]

I have now resolved the problem this way:

SELECT TARDIS_Members.Email, tblDatabase.Email
FROM TARDIS_Members LEFT JOIN tblDatabase ON TARDIS_Members.Email =
tblDatabase.Email
WHERE ((Not (TARDIS_Members.Email)="(e-mail address removed)") AND
((tblDatabase.Email) Is Null));

Thanks

Robin