Fields with trailing spaces.

[kidrobot]

I have tables that get their data from SQL server. Some of the fields in the table have trailing spaces. Does this issue need to be resolved in SQL server or in Access?

 

[boblarson]

If you are linked to the data then you can just create a base query that uses the Trim function to remove those spaces. If you are importing then use the Trim function in an update query after importing.

Of course, if you can get the data cleaned up in SQL Server then it will be even better.

 

[kidrobot]

If you are linked to the data then you can just create a base query that uses the Trim function to remove those spaces. If you are importing then use the Trim function in an update query after importing.

Of course, if you can get the data cleaned up in SQL Server then it will be even better.

Thanks, so is there a trim function that can trim trailing spaces even though the characters in the field are different lengths?

 

[Brianwarnock]

Lookup Rtrim in VBA help, but the answers is yes

Brian

 

[The_Doc_Man]

Part of this problem may also be the declaration in SQL server. I'm not up on this for SQL Server but in ORACLE you can declare either fixed or variable text strings. (The latter is an ORACLE VARCHAR.) If the SQL Server definition doesn't allow varying field lengths (up to some maximum) then you might be getting back fixed-length strings because that is what was declared.

Concur with the advice of TRIM$() and its variants.