TRIM function isn't working in my query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003 on Windows XP machine:

I've imported some data from a text file. The EEName field is 30 characters
long, and the data that was brought in is padded with spaces.

To trim the extra spaces off, I added a new field, and ran an Update Query,
to update the NewField to TRIM([EEName]).

NewField is still padded with spaces - exactly the same as the original
EEName.
I can copy the value of an EEName field, and copy it as a literal into the
command window with the command:
?trim("LAST, FIRSTNAME ")

and I will get the trimmed name as a result, so I know the TRIM function
works in my Access install.

It wasn't really necessary, since the TRIM function worked in the Command
Window, but I tested the last character of the EEName field, and the ASCII
value is 32, so I know it is a space.


I'm stymied.
Any suggestions?
 
Did you import the data or did you link to the text file?

If you imported the data did you import into an Access database and
table or did you import into an MS SQL table?

If you imported into an MS SQL table, check the field definition. If it
is Char then it will be padded to the length of the field. If it is
varchar then you should be able to remove the spaces.

Did you run the update query or did you just switch views? Unless you
run the update query (Query: Run from the menu or the red Exclamation
mark on the menubar) the query does not execute, it only shows you the
records and fields that will be affected if you should run the query.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
I imported the data into an Access table.
I then created a new field, and updated that field with an update query that
used the Trim function to strip off the spaces padded as filler in the
original data.

When I do a Len([field]) function on the fields, both fields in all records
are 30 characters long.



John Spencer said:
Did you import the data or did you link to the text file?

If you imported the data did you import into an Access database and
table or did you import into an MS SQL table?

If you imported into an MS SQL table, check the field definition. If it
is Char then it will be padded to the length of the field. If it is
varchar then you should be able to remove the spaces.

Did you run the update query or did you just switch views? Unless you
run the update query (Query: Run from the menu or the red Exclamation
mark on the menubar) the query does not execute, it only shows you the
records and fields that will be affected if you should run the query.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Access 2003 on Windows XP machine:

I've imported some data from a text file. The EEName field is 30 characters
long, and the data that was brought in is padded with spaces.

To trim the extra spaces off, I added a new field, and ran an Update Query,
to update the NewField to TRIM([EEName]).

NewField is still padded with spaces - exactly the same as the original
EEName.
I can copy the value of an EEName field, and copy it as a literal into the
command window with the command:
?trim("LAST, FIRSTNAME ")

and I will get the trimmed name as a result, so I know the TRIM function
works in my Access install.

It wasn't really necessary, since the TRIM function worked in the Command
Window, but I tested the last character of the EEName field, and the ASCII
value is 32, so I know it is a space.


I'm stymied.
Any suggestions?
Click to expand...
Click to expand...
 
Next guess.

Perhaps the last trailing character is not a space but some other
invisible character. But you tested for that and got 32 which should be
correct. Asc(Right(SomeField,1)) returns 32

And you tested that Len(Trim(SomeField)) is not equal to Len(SomeField).

This is puzzling...



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I imported the data into an Access table.
I then created a new field, and updated that field with an update query that
used the Trim function to strip off the spaces padded as filler in the
original data.

When I do a Len([field]) function on the fields, both fields in all records
are 30 characters long.



John Spencer said:
Did you import the data or did you link to the text file?

If you imported the data did you import into an Access database and
table or did you import into an MS SQL table?

If you imported into an MS SQL table, check the field definition. If it
is Char then it will be padded to the length of the field. If it is
varchar then you should be able to remove the spaces.

Did you run the update query or did you just switch views? Unless you
run the update query (Query: Run from the menu or the red Exclamation
mark on the menubar) the query does not execute, it only shows you the
records and fields that will be affected if you should run the query.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Access 2003 on Windows XP machine:

I've imported some data from a text file. The EEName field is 30 characters
long, and the data that was brought in is padded with spaces.

To trim the extra spaces off, I added a new field, and ran an Update Query,
to update the NewField to TRIM([EEName]).

NewField is still padded with spaces - exactly the same as the original
EEName.
I can copy the value of an EEName field, and copy it as a literal into the
command window with the command:
?trim("LAST, FIRSTNAME ")

and I will get the trimmed name as a result, so I know the TRIM function
works in my Access install.

It wasn't really necessary, since the TRIM function worked in the Command
Window, but I tested the last character of the EEName field, and the ASCII
value is 32, so I know it is a space.


I'm stymied.
Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top