Query to trap typos & misspellings etc

[aravindk]

Hello all,

I have an application where the users have made mistakes while typing a keyfield. For example, in the Customer transactions table, when they have to input a 10 digit customer id, they make a typo: Instead of entering 007892012, they enter 0072892021 or 007982012. Is there a way of building a query whereby I can return records with "similar" customer id? I tried using like operator, but the results are not very effective.

Has anybody done this before? Any help is greately appreciated.

 

[BNPMike]

Why would a similar number help you?

Don't you want the use to type in a number that must already exist in the database?

 

[aravindk]

Well, ideally, yes. This is an outdated system and we have to use to run some reports. What has happened here is the Customer ID is generated by a different department and then the users are supposed to enter the id manually into this access database. Some people made mistakes while keying in.

 

[lespaul]

Do you have the ablility to join into the database where the correct numbers are? If so:

SELECT * FROM WRONGTABLE WHERE KEYFIELD NOT IN (SELECT KEYFIELD FROM RIGHTTABLE)

 

[aravindk]

The Righttable has the codes that were generated since last year. Before that we didnt have that table. The Customer IDs were generated manually by keeping track of them in a spreadsheet...