Query to flag up duplicates

A

Alex Shirley

Hi

I have a query which almost works fine. It is supposed to compare the
current record being displayed on the form with similar records on a
contacts table. Its purpose is to flag up any possible duplications of
the record (checking for similar names, postcodes and email
addresses):

SELECT Contacts.ContactID, Contacts.FirstName, Contacts.Initial,
Contacts.LastName, Contacts.CompanyName, Contacts.Postcode,
Contacts.Email
FROM
Contacts
WHERE
Contacts.email=txtEmail.Value
Or
txtFirstName.value & txtLastName.value=Contacts.FirstName &
Contacts.LastName
Or
txtPostcode.Value=Contacts.Postcode
Or
txtCompanyName.Value=Contacts.CompanyName
And
txtContactID.Value<>Contacts.ContactID;

The query works fine with one exception. The current record being
displayed on the form is always being displayed as a possible
duplicate. It should be excluded. I was hoping this part of the query:

And txtContactID.Value<>Contacts.ContactID; (note txtcontactID holds
the unique identifier of the record displayed)

…would exclude the current record being displayed on the form;
unfortunately it doesn't. Where am I going wrong? Is there a simple
solution?

Many Thanks

Alex
 
Top