H.3. What Are Special Characters?

Special characters are characters that are interpreted by Access, SQL Server, and VBA as field type delimiters, 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. (OK, we do concede that there are different guidelines for naming VB procedures, variables, and constants than for database objects and field names, but it may not hurt to apply the union of the two sets of rules to both situations.)

Looking at the list of special characters, it can be obvious why some should be avoided. For example the "." (period) can be dastardly combined with a reserved word.

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 will cause the VBA to choke as it will interpret the single quote as the beginning or end of a string. Obviously, since it is being used as an apostrophe, there is nothing to close the string.

In addition to the following two lists of characters to avoid, there are a couple more seemingly innocent things that can turn into gotcha's. We strongly recommend avoiding spaces in field names and starting field or column names with a numeric character. For example, field names such as 2ndPhone and Area Code ...

Get Access 2003 VBA Programmer's Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.