Find and Replace

[dweekley]

I am looking to use an Update Query to correct spelling errors. I have to approach this way due to limited access to Access functions. Having trouble searching for the one misspelling and keeping all the surrounding text.

[Rawb]

I'm not sure what you want. You're not asking a question...

[dweekley]

For instance...the word 'instruction' is spelled 'instuction'. Along with many other misspellings. I do not have access to the replace function in Access so I want to use an Update Query to correct all the known misspellings in a memo field.

[Rawb]

I'm assuming your question, then, is "How do I do this?"

I wouldn't use Queries. I'd go straight to VBA code that modifies the Memo Field from a Recordset.

Correcting misspellings is one of the hardest things to do because there's no "one way" to spell something wrong. That means that the most effective way to catch them is to have a list of every word there is and, one at a time, compare each word in your Memo Field with this list to make sure there aren't any that don't match.

And that's the easy part. Because once you find a word that doesn't match anything in your list, you have to be able to figure out what word they actually meant before updating it.

What I would do (and this will be a fairly large project) would be to:

1. Make a Table with a single Field and store every word in the English language in it.
2. Then make a function that compares a word (passed through an argument) against similarly spelled words in your list and returns the closest match.
3. Then make another Function that tokenizes the Memo Field and checks each word against the list. When it finds a word that doesn't match, it passes it to the first function to find a replacement word.

You may be able to make this quicker by finding a word list that you can just dump into your database. The comparison function you will likely have to do yourself though: Your best bet there is to find the comparison method you want, find a function that uses it, and then just modify that function for your specific need.