What Are Special Characters
Special characters are those that are interpreted by Access, SQL Server, and VBA as field type delimiters, as the introduction of a comparison function, or other instructions. Therefore, special characters and control characters (ASCII values 0 through 31) should not be used as part of the name of a database field, object, variable, procedure, or constant. (Okay, we do concede that there are different guidelines for naming VB procedures, variables, and constants than for database objects and field names. But it seems sensible to combine the two sets of rules and apply them to both situations.)
Looking at the list of special characters, it is obvious why some should be avoided. For example, the . (period) can return unexpected results when used with a reserved word. For example, given a field Name in table Students, the syntax Students.Name would return the value of the table's Name property instead of the value in the Name field.
Similarly, putting an apostrophe in a field name causes the VBA to choke as it interprets the single quote as the beginning or end of a string. Because the ' is being used as an apostrophe, there is nothing to close the string until VBA comes to the next apostrophe (which is likely meant to start another string).
In addition to the following two lists of characters to avoid, there are a couple more seemingly innocent things that can turn into gotchas:
□ Do not put spaces in field names. For example, field names such as 2ndPhoneand Area Code could cause unexpected hiccups. If you insist on separating words, use the underscore (a grudgingly acceptable option).
□ Do not start field or column names with numeric characters.
Remember that an object or field name cannot begin with a space. Access immediately advises you of the error if you try to put certain special characters in a field name. Figure K-3 shows the error message generated by trying to create a field name with a leading space. Notice, however, that Access accepted other special characters within the field name. This may create a false sense of well-being because, as pointed out earlier, a name containing a special character requires special treatment throughout the application.
_I ACCESS RESERVED' j tbINames
Field Name Microsoft Office Access
LeadSpaceLayout TrailSpace
LeadSpaceDesignView
Figure K-3
The field name is not valid.
Make sure that the name doesn't contain a period ( ¡, exdamation point (!), bracket {[]), leading space, or non-printable character as a carriage return. If you have pasted the name tfom another application, try pressjig ESC and typng the name again.
Help
Special characters not only wreak havoc in code, they can cause problems if they are in text and memo fields. Most of these special characters put the breaks on a word search. An application that has been working smoothly for months may suddenly throw error messages when the user runs a search on a text field. For example, Kim's Curry House, as the name of a business will likely stop a search. The apostrophe causes the SQL interpreter to "think" that a string has been initiated or ended. Solution: Use code to prevent users from entering special characters into text and memo fields. See the code example at the end of this appendix.
Special Characters to Avoid
|
Key |
ASCII |
Name |
|
44 |
Comma | |
|
46 |
Period | |
|
; |
59 |
Semicolon |
|
58 |
Colon | |
|
96 |
Grave (open single quote, backtick) | |
|
39 |
Apostrophe (single quote) | |
|
" |
34 |
Quote (double quote) |
|
? |
63 |
Question mark |
|
/ |
47 |
Solidus (slash) |
|
> |
62 |
Greater than |
|
< |
60 |
Less than |
|
[ |
91 |
Left square bracket |
|
] |
93 |
Right square bracket |
|
{ |
123 |
Left curly brace |
|
} |
125 |
Right curly brace |
|
\ |
92 |
Reverse solidus (backslash, whack) |
|
I |
124 |
Vertical bar (pip, pipe) |
|
~ |
126 |
Tilde |
|
! |
33 |
Exclamation mark |
|
@ |
64 |
Commercial at |
|
# |
35 |
Number sign (pound, hash) |
|
$ |
36 |
Dollar sign |
|
% |
37 |
Percent |
|
A |
94 |
Caret |
|
& |
38 |
Ampersand |
|
* |
42 |
Asterisk |
|
( |
40 |
Open parenthesis |
|
) |
41 |
Close parenthesis |
|
= |
61 |
Equal sign |
|
+ |
43 |
Plus sign |
ASCII Characters to Avoid
You often see the term ASCII, but its full name is seldom spelled out, so here it is in plain English: American Standard Code for Information Interchange. Computers are number-driven, and ASCII code is the numeric representation of characters or actions. The first 32 ASCII characters are actions or nonprinting characters, which is why using any of these characters as the name of an object or function would be interpreted as an instruction and could initiate unexpected actions. Most developers recognize ESC, CAN, NUL, LF, CR, and TAB as commands, but many of the other ASCII characters have been forgotten. The following table, then, has a dual purpose. It's a handy reference for knowing what characters to avoid, and it's a useful resource for when you want to include an action such as inserting a carriage return and line feed in your VBA. The character names have been incorporated into the list of words to avoid.
Post a comment