There’s an old saying that goes “You learn something new every day”. That’s fine, but it’s embarrassing when what you learned that day is something that you should have learned years ago.
I recently was working with Access when I ran into a situation I’ve never run into before. As I did some research, it became clear that this “problem” I was having seemed to be common knowledge.
It revolved around special characters. Since we all have things to do today, I’ll simplify the scenario to a basic example.
I was working with a table similar to this one, where there are number symbols (#) peppered throughout the values.
I decided to find all the values that have the number symbol just to see how many I was dealing with.
So I wrote this query. As you can see, I’m using wild card characters to tell access to return values that contain a number symbol .
This is what Access returned. You’ll notice that Access didn’t just return the values with number symbols as expected – it returned even those values that don’t have number symbols.
At this point, I’m thinking “What the heck? Did I find a bug in Access? Did I make a mistake in writing the query? What’s the answer?”
As it turns out, the answer was that I was having a brain fart.
You see, the number symbol is a special character used in Access queries to indicate that you want any number value. So setting the criteria (LIKE *#*) will return any value that contains any number in it. Of course I knew this, but I didn’t put two and two together initially.
So after doing a little research, I learned that if you are querying for “special characters”, you’ve got to wrap the offending character in Brackets (). This query will return the correct results because the number symbol is wrapped in Brackets.
Obviously, the moral of this story is to avoid using special characters if you have a choice.
So what are the other “special characters” you have to watch out for?
Well according to Microsoft:
|Greater than sign||>|
|Less than sign||<|
In Excel, the only special characters that have caused me problems are the Asterisk (*) and the Question Mark (?).
These are the wildcard characters in Excel. They typically wreak havoc when trying to Find and Replace.
For example, If you try to find and replace all asterisks (*) with a hyphen as shown in this screenshot, you’ll turn your entire spreadsheet into hyphens.
This is telling Excel to replace everything with a hyphen.
When you’re trying to Find an asterisk or question mark, prefix it with a Tilde (~) symbol. This way, you won’t get fired.