Trim function not working

[Peter B]

I am trying to remove trailing spaces from a field. In a query based on a
table I have used the expression: Spcs: Trim([Species]) . This
has made no difference to the length of the entries in the field and the
trailing spas have not been eliminated.

Can anyone help, please?

Peter

 

[Douglas J. Steele]

Where is the data coming from? Is it possible that it's not spaces in the
field, that it's, for instance, Null characters?

 

[Peter B]

A long story, so bear with me please.

I want to import a list from Excel to Access.

I have an excel file with a list in a column in the form: aaaa aaa
bb bbbbb where the gap between the 2 sets of characters I assume are
spaces. The 2 halves are of varying length and within each half there
individual spaces separating words. I wish to retain only the first half
(that is the aaaa aaa bit). The maximum length of this half is 35
characters, so in excel I used the expression of the form RIGHT(D20,35) in
an adjacent column to eliminate the 2nd half, but this of course will reatin
a varying number of trailing spaces. I then copied the new column to a new
sheet.

In access in an exisiting database I imported the new sheet to create a new
table with the filed species. I then created a query using the expresson
given below ( Spcs: Trim([Species]) ). As I said, this does not
eliminate the trailing spaces.

I did incidentally try to remove the spaces in Excel with the TRIM function
at the same time as eliminting the b's, but this does not work either, so I
might have a universal MS Office problem. I have also posted a similar
query to the Excel news group.

Peter

 

[Tom van Stiphout]

On Wed, 16 Jul 2008 13:28:03 +0100, "Peter B" <[email protected]>
wrote:

Try this in the Immediate window:
?Len(DLookup("Species", "tblSomeTable", "ID=1"))
(of course you modify this with your table name, primary key field
name and value)
Then take the same expression and trim it:
?Len(Trim(DLookup("Species", "tblSomeTable", "ID=1")))
If there is something to be trimmed, it should now have happened and
the value returned is smaller.
Perhaps what you think are spaces are not, or they are not at the very
beginning or end of the string. Test that by getting the ascii value
of a character:
?Asc(Mid((DLookup("Species", "tblSomeTable", "ID=1"),1,1)
It should be 32 for a space.

-Tom.

 

[Douglas J. Steele]

Use Tom's suggestion to try and determine whether your data has some other
character. Just one comment. Recognize that when Tom had

?Asc(Mid((DLookup("Species", "tblSomeTable", "ID=1"),1,1)

that will will tell you about the first position in the string, which
presumably isn't where the problem is. You'll need to play with setting the
value of n so that it's one character past what you think is the last
non-blank character.

?Asc(Mid((DLookup("Species", "tblSomeTable", "ID=1"),n,1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A long story, so bear with me please.

I want to import a list from Excel to Access.

I have an excel file with a list in a column in the form: aaaa aaa bb
bbbbb where the gap between the 2 sets of characters I assume are spaces.
The 2 halves are of varying length and within each half there individual
spaces separating words. I wish to retain only the first half (that is
the aaaa aaa bit). The maximum length of this half is 35
characters, so in excel I used the expression of the form RIGHT(D20,35)
in an adjacent column to eliminate the 2nd half, but this of course will
reatin a varying number of trailing spaces. I then copied the new column
to a new sheet.

In access in an exisiting database I imported the new sheet to create a
new table with the filed species. I then created a query using the
expresson given below ( Spcs: Trim([Species]) ). As I said, this
does not eliminate the trailing spaces.

I did incidentally try to remove the spaces in Excel with the TRIM
function at the same time as eliminting the b's, but this does not work
either, so I might have a universal MS Office problem. I have also
posted a similar query to the Excel news group.

Peter

Where is the data coming from? Is it possible that it's not spaces in the
field, that it's, for instance, Null characters?

 

[Peter B]

Thanks everyone for yout ideas. In the end it was was solved on the Excel
group, where someone spotted that the original souce might be html and
provided a TRIM(SUBSTITUTE....) function which has solved the problem.

Peter