Microsoft KB Archive/95009

= ACC: Trailing Spaces Automatically Truncated During Data Entry =

Article ID: 95009

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q95009



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



SYMPTOMS
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 that 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 the following:

  FieldName & Space(TotalWidth - Len(FieldName))

You could similarly use the Space method to add a consistent number of spaces to the end of any field with a query expression similar to the following

  FieldName & Space(x)

where x is the number of spaces you want to add to the field.



STATUS
This behavior is by design.



MORE INFORMATION
Although both &quot;a&quot; or &quot;a &quot; are acceptable input in the example below, the data stored in the underlying table will always contain the &quot;a&quot; value. The space following the &quot;a&quot; 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:

=&quot;a &quot;
 * 1) View the form in Form view.
 * 2) Enter &quot;a&quot; (without the quotations marks and no space) in the field. Note that you do not receive a validation-rule-violation message.

In Microsoft Access 7.0 and 97:


 * 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:

?&quot;&quot;a&quot;=&quot;a &quot;

- or -

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

Note that False is returned for either statement.

In Microsoft Access versions 1.x and 2.0:


 * 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:

?&quot;a&quot;=&quot;a &quot;

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

Additional query words: validation

Keywords: kbprb kbusage KB95009

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.