Duplicate records

[jckcrtr]

Hi guys,

Currently I have input masks set so no duplicates can be inputted however I've still got one or two due to the way they've been inputted. E.G if a phone number is 01952 123456 it can still be inputted as 019521 23456. Is there any way I can find duplicates like this that currently exist so they can be removed? Thanks.

 

[billmeye]

You might consider adding an additional field to your table, PhNumDigOnly, and use Replace("01952 123456"," ",vbnullstring) to return just all the digits. You can use an Update Query to fill this in with your existing data. Then run a Duplicate check on this new field.

In the future, you can then use that new field for easy comparison in the PhoneNum fields BeforeUpdate event:

If Dcount("*","tblContacts","[PhNumDigOnly]='" & Replace(Me.PhoneNum," ",vbnullstring) & "'") > 0 then
Msgbox "Duplicate"
Me.PhoneNum.undo
Cancel = 1
End If

In the AfterUpdate event you can then store the new field value:

Me!PhNumDigOnly = Replace(Me.PhoneNum," ",vbnullstring)

 

[jckcrtr]

Thats great I'll do that, thanks very much for the help!