Unwanted trailing spaces in form

[Adam]

I have created a form with a combo box, bound to a text field. The row
source of the combo box comes from another table. The trouble is that Access
(2000) seems to add extra spaces to the values in the combo box, so that
they match the maximum possible field size for the text field. These extra
spaces are then stored in the field which is bound to the combo box.

Is there any way to stop this happening? I really don't need these extra
spaces.

Many thanks

 

[Brendan Reynolds]

Access doesn't do that, Adam, or at least, that is not the default behaviour
of an Access combo box.

The first thing is to determine whether the spaces are actually stored in
the table, or whether they only appear in the row source of the combo box.
The following SQL statement would establish for sure whether the trailing
spaces are actually stored in the table:

SELECT SomeField FROM SomeTable WHERE Len(SomeField) <>
Len(RTrim(SomeField))

If the spaces are actually stored in the table, are they in all records? Or
are they, perhaps, only in older records that may have been imported from
another data source? Is the data actually stored in Access tables at all, or
are you linking to an external data source?

If you can establish that information, please post a reply here, and I'll
try to investigate further.

 

[Dave]

use trim() function

 

[Adam]

Thanks for those thoughts, Brendan.

The table from which the combo box gets its row source definitely has no
trailing spaces. I tried the SQL statement you suggested below on that
table, and it did not identify any records. And yes, the table is an Access
table stored within the same database as everything else.

The form with the misbehaving combo box is linked to a different table. When
I use the form to enter data into the table, the trailing spaces are stored
in that table. Again, I tried your suggested SQL, and it picked up all the
records.

Interestingly, I also tried using the Trim() function in the SQL for the
record source of the combo box, but that made no difference.

Another piece of information that may be useful is that when an item is
selected in the combo box, the selection extends past the end of the word in
the combo box (ie including some trailing spaces), suggesting that it is
something to do with the combo box itself that is creating the spaces,
rather than the point where it is saved to a new record.

Any more thoughts?

Many thanks

Adam

 

[Brendan Reynolds]

Could you post the SQL for the row source of the combo box?

 

[Adam]

Of course. It's as follows:

SELECT [Value] FROM sVariableValues WHERE VarID=14 ORDER BY [Seq];

Nothing very fancy, really.

Adam