I need to find similar values

[thallone]

I am looking for a good way, either VBA or SQL that I can use to find similar values in a Name field. For example, if I give the entry 'John Smith', I would like to find not only all entries with the string John Smith, but also Jon Smith and John Smyth. By the same token, I would like to return complete name subsets that exist. Thus someone only listed as John would be returned by the search, and 'The United States of America' would return records named 'The United States" etc.

Other than going in ans doing searches brute-force style by replacing characters with wild-cards, I can't see how to do this. Can anyone give me any ideas?

[andi_g69]

In T-SQL you have the SOUNDEX function which should find similar records Like

Smith, Smyth,...

I do not have a clue if there is an equivalent in Access.

If you want to find records which match on subsets, you could do it like this:

I take your example of "John Smith" as search string:
In T-SQL:
SELECT * From tblNames
WHERE 'John Smith' LIKE sName + '%'

In Access:
SELECT * From tblNames
WHERE 'John Smith' LIKE sName + '*'

[PinkPanther2003]

The soundex suggestion is a good one IMO.

There are various websites containing the soundex in VB/VBA.

Unfortunately JET doesn't have a built in soundex code.

Do a search on google.

HTH,

Peter