Special Characters are a Pain in the Asterisk

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:

Space  
Apostrophe
Quotation mark
Apostrophe
At sign @
Grave accent `
Number sign #
Percent %
Greater than sign >
Less than sign <
Exclamation mark !
Period .
Brackets [ ]
Asterisk *
Dollar sign $
Semicolon ;
Colon :
Question mark ?
Caret ^
Braces { }
Plus sign +
Hyphen -
Equal sign =
Tilde ~
Backslash \

.

Note:

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.

6 thoughts on “Special Characters are a Pain in the Asterisk

  1. Oakhome

    Thanks for that very handy tip on replacing asterisk with something else in Excel. Figuring this out has been on my ‘to-do’ list, but not done!

  2. Nick Burns

    How apropos! I was dealing with a spreadsheet yesterday where the user had marked columns with an asterisk (*) to indicate Yes. It took me awhile to figure out how to change the characters to something else (either a Y or an X) without changing the entire worksheet!

  3. Paul

    That’s funny… I remember learning that once before too. It’s obvoius that I have since forgot that little nuiance since I wind up doing iteratve gymnastics to work around those “special” characters… maybe I’m just special. :)

  4. melissa

    awesome. this made my day, my spreadsheet, and my job 10000 times better. you saved me hours worth of work. high five!

  5. Ben Kusmin

    The ampersand (&) also creates problems in Excel, particularly when you try to use one in a header or footer. The solution is to use two of the little buggers. Thus, if you want the header to say “Profit & Loss” you will type “Profit && Loss” into the Custom Header field:
    Huzzah!
    Ben Kusmin, a/k/a Excel Esquire

  6. Hagridore

    My problem is that the special character, in my case /, shows up as part of the value stored in a field. When I try to run a filter where this value is one of the search criteria I get an error about the syntax of my expression being incomplete. I’ve tried double quotes and square brackets around the / and a \ in front of it to no avail. I don’t have the option of changing the value…that’s been set by the multi-billion dollar project I’m working for. I thought that Access would ignore any value in the fields but that’s obviously not the case. I’ll really appreciate any help I can get on this.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>