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

Tricky Search Problem Easy Solution with a Literal Search

Introduction: A Problem that is Easy to Solve When You Stop Hitting it Head On

Check out the puzzle below. If you start along the lines of looking for an arithmetic solution, you could spend time noting there is an apparent pattern in the instances of the numbers 6 and 8.

The solution is to take a step back and look at it in another way. Once you realize that the numbers are oriented toward the incoming cars, it becomes obvious that the numbers from our point of view are upside down – the missing number is clearly 87.
A similar problem you may encounter in Microsoft Access is trying to search on the asterisk symbol “*”

This is not easy to solve since the asterisk symbol has a special meaning – it’s a wild card, \any set of characters meets the criteria.

The Problem

Let’s say you have the following table of animals that you inherited from someone else. The person who created this list chose to identify birds with the * symbol. Let’s further say that there are 1,000 records in this table. How do you retrieve those records that have * as their final character?

There at least three solutions to this problem I’ll give you one solution this month, and others in subsequent Wizards.


The Solution: A Literal Search

In the above problem if you use a query criteria,

SELECT AnimalName FROM tblAnimal where AnimalName) Like "*"

You get all records because the * symbol mean any set of characters.

This is a case where you must approach the problem from a different point of view. Instead of using a wild card, you use a literal search, which means, ignore any special characters and return the records for the exact character typed in. You do this by enclosing the asterisk symbol (*) in brackets. The search criteria then becomes:

Select AnimalName from tblAnimal where tblAnimal.AnimalName Like '*[*]*'

This returns only the records that have the asterisk someplace in its name.

Once you include the brackets with the bracketing technique, the search becomes much more straightforward.

Tip of the Month: Literal Search for Other Symbols

In this month’s Wizard, I showed you how to do a search with an asterisk using the brackets to indicate a literal search. There are other special characters that will also cause search issues. The following are some of the more common ones you may trip over:  ?  -  #  =

If you find yourself having to search on any of these, or if you are searching on any unusual symbol and it isn’t working properly, try using a literal search with the brackets to see if this is an easy solution to your problem.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

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