The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Multiple Solutions to the Same Search Problem


Frequently, there’s more than one solution to a specific problem. When you get stuck trying to solve a problem and believe there is only one solution, step back and give the problem more thought. In most cases, your initial approach, although logically correct, may not be the only (or best) solution.

This month, I will show you an entirely different method to solve the problem I presented in the last Wizard – dealing with the pesky * character in queries. The goal is to help you when you hit a roadblock: Look for another solution.

The Problem and First Solution

in the last Wizard, I showed you how to search on the * character.  The solution was to use the literal approach in the search criteria. You can find the entire problem and solution in the December Wizard.

Alternate Solution: String Reverse and Isolate

There’s a function in Microsoft Access strReverse, which takes whatever you give it and returns the characters in reverse order. For example, using StrReverse("god") returns “dog”. You can use this function in a query on any text field.

Remember from last Wizard, we had the table below and wanted to isolate the records where the animal names had an asterisk character at the end.

In the query below, I have taken our animal table and put it into a query, then used the strReverse function on the Animal Name field and sorted on result of the function. 

When we run this, we get the birds (entries ending with an asterisk) at the top of the list.


From here, I clicked on the last record that has an asterisk, “Flamingo*”, and noted that it is the third record in the list. At this point, we need to isolate the top 3 records so that we have only the results we want.

Back in the design view, we limit our results to the top 3 records by clicking in the grey area in the top half of the screen so the popup box below appears.

When we click on the properties option, we see the property sheet below.

We change the top values (which says “All” by default) to 3, run the query, and get

At this point, you have what you need in a query that you can now act on.


I have shown you a new way to solve the same problem we started with: isolating and acting on a set of records that are difficult to get to using standard approaches.

Although these are specific instances to a specific problem, I urge you to start thinking outside the box when you are faced with a difficult data management problem. Very frequently, there is more than one solution.


Tip of the Month: Another Solution Use Find and Replace to Make Troublesome Characters Go Away

Another solution to working with tricky characters is to run a find and replace.

In your table, you can highlight a column of data, click Control-F to bring up the Find Box, click on the Replace Box, and then replace your troublesome character (in our case it was the *), with some value that you know will not be in your data “ABC123” for instance, then use the standard methods to manipulate your data.

Of course, at the end of the process you will want to remember to remove the characters you added along the way.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886