ACC: Trailing Spaces Automatically Truncated During Data Entry

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

If you enter data that has one or more trailing spaces in a form control, Microsoft Access ignores the trailing space(s); therefore, the data you enter may not be valid as a means of comparison. Also, if variables in Visual Basic for Applications (or Access Basic in versions 1.x and 2.0) have been assigned a value containing a space character at the end of the text string, Microsoft Access will ignore the space character when the variables are used in a conditional statement.

CAUSE

Microsoft Access automatically truncates (or strips) trailing spaces on data you enter in form controls to prevent the creation of a record that cannot be referenced because of accidental space character(s) being appended to the end of the text.

RESOLUTION

Do not use text strings that contain trailing spaces. If you need trailing spaces, you can add them through the use of a query and then use the query in place of the table when accessing the data.

In a query you can simulate a fixed-width field by using the Space method to add the required number of spaces to pad the field. If you have a field containing text and you want all fields to contain the same number of characters, you could use a query expression similar to this:

   FieldName & Space(TotalWidth – Len(FieldName))

   FieldName & Space(x)

STATUS

This behavior is by design.

MORE INFORMATION

Although both "a" or "a " are acceptable input in the example below, the data stored in the underlying table will always contain the "a" value. The space following the "a" is truncated.

Steps to Reproduce Behavior

In a form:

1. Open any database.

2. Create a form with a single text field.

3. Enter the following for the field's ValidationRule property:

         ="a "
   

4. View the form in Form view.

5. Enter "a" (without the quotations marks and no space) in the field. Note that you do not receive a validation-rule-violation message.

1. Open any database.

2. Press CTRL+G to open the Debug window.

3. Enter the following lines in the Debug window, and press ENTER after each statement:

         ?""a"="a "
   

         - or -
   

         ?chr(97)=chr(97)+chr(32)
   

   Note that False is returned for either statement.

1. Open any database.

2. Open a new module and click Immediate window on the View menu.

3. Enter the following lines in the Immediate window and press ENTER after each statement:

         ?"a"="a "
   

          -or-
   

         ?chr(97)=chr(97)+chr(32)
   

   Note that either statement will return a non-zero number indicating the statement is true, which means the values are equal.