Access™ 2007 VBA Programmer's Reference

K.4. 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 ...