Working with MS Access functions (of the built-in variety), you may have noticed a vast list of functions across different categories. By combining (or nesting) functions, you can certainly produce complex output values which will serve you well with your queries, forms and reports.
However, there is one lacking feature (or function) in Microsoft Access that could hinder searching for values and that is a function that can find similar or ‘sounding like’ a string match of characters. This function is widely known as ‘Soundex’ and some database applications do provide their own algorithm to support this technique (including the Soundex SQL Server function).
The algorithm is a phonetic list of character strings based on the pronounced English language and of course this can be deemed very subjective indeed hence why there are no real set of rules here – it’s a matter of just how you want to provide the best balance and how many character strings (length) do you want to include.
MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function
To save on time and giving credit to other Access experts around, please go and check out Allen Browne’s Soundex – Fuzzy matches article which provides his version of Access VBA code.
Here’s a snapshot of the code:
You will need to go to the provided link (above) and copy the code into a module and follow his instructions.
The way the function roughly works is as follows:
- A string value is supplied i.e. someone’s first name ‘Alan’.
- The soundex function is called which has one argument called varText and passes the value; in this case ‘Alan’ to the function procedure.
- A quick error check to see if there is a value and gracefully handle zero length strings or nulls have been added before proceeding.
- Three variables are set; strOut, strPriorValue and lngPos ready for rebuilding the output string and algorithm value from the parameter varText input.
- There is a loop which iterates through each character one a time to output the first character and then a value representing a similar character match (which is numeric) for the remaining character length. This is where the main portion of the work is carried out as it calls another function called SoundexValue to assign a value in this case with three digits along with the first character (i.e. A450 for Alan).
- Finally the function returns the variable strOut that was built up out to the external object (query, form or report).
Since the output length can be adjusted beyond a three digit match, you can appreciate how subjective this function can be and why different systems use different algorithms.
By all means tweak the code (I’m sure Allen Browne provided this free gratis!) and see how your MS Access functions can be used and refined for text searching.
To use it, simply create a query and wrap the custom function around a parameter prompt for your string input – see below:
Run the query and enter a first name in the pop-up prompt. I entered ‘Alan’ and the following from my list of student names provided similar matches as shown below:
Of course you can use this with reports and forms too and don’t forget your macros and modules (VBA) can utilise and nest your new custom built function alongside the built-in ones.
One of my offers called Microsoft Access Database Search Tool Utility provides an easy to use and edit interface with flexible and accessible VBA code where this could serve you well to include an option for a ‘sound like’ checkbox to include the Soundex function call.
All my offers including the eBooks come with a no questions asked money back guarantee – give it a go!
Tags: access vba functions, creating functions in access, custom built microsoft access functions, how to build a vba function, MS Access, ms access functions