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.
