Trim Field in Table

[Jani]

Didn't know where to put this. Is there a way to Trim a field in a table?
Thanks! Jani

 

[Jani]

So one field trimmed perfectly. Two other fields will not Trim. I even moved
the data to Excel and tried trimming with no luck. I'm baffled. Any idea why
a field would not trim? I'm using "trim([field name]). Should be so simple,
right! Thanks,

 

[Jani]

It isn't spaces - but can't tell what they are. Now what?

It is possible that those fields aren't padded with spaces, as they appear to
be. They could be padded with special characters, like tabs, carriage
returns, etc. that appear to be spaces.

Try copying the text of one of the fields in question into Word and see what
it looks like there.

So one field trimmed perfectly. Two other fields will not Trim. I even moved
the data to Excel and tried trimming with no luck. I'm baffled. Any idea why
a field would not trim? I'm using "trim([field name]). Should be so simple,
right! Thanks,

Do an update query. In the UpdateTo parameter of the field you want to trim,
put your trim function.

Didn't know where to put this. Is there a way to Trim a field in a table?
Thanks! Jani

 

[Jani]

No - it's not tab or carriage returns. Looks like superscript 0 or O's.
Perhaps you can copy the word below and see what they really are:
ADVANCE

Do they look like squares?

They might be tabs or carriage returns. Try the replace function:

Replace([Field],chr(13),"")
Replace([Filed],chr(10),"")

It isn't spaces - but can't tell what they are. Now what?

It is possible that those fields aren't padded with spaces, as they appear to
be. They could be padded with special characters, like tabs, carriage

[quoted text clipped - 13 lines]

Didn't know where to put this. Is there a way to Trim a field in a table?
Thanks! Jani

 

[John Spencer]

Try using this expression on the field to get the ascii code of the last
character in the field.

ASC(Right([TheField],1))

Then you could try
Replace([TheField],Chr(ASC(Right([TheField],1))),"")

One possibility is that the last character is a carriage return Plus a line
feed. So to get rid of that you could try the following expression
Trim(Replace([The Field],Chr(13) & Chr(10),""))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

No - it's not tab or carriage returns. Looks like superscript 0 or O's.
Perhaps you can copy the word below and see what they really are:
ADVANCE

Do they look like squares?

They might be tabs or carriage returns. Try the replace function:

Replace([Field],chr(13),"")
Replace([Filed],chr(10),"")

It isn't spaces - but can't tell what they are. Now what?

It is possible that those fields aren't padded with spaces, as they appear to
be. They could be padded with special characters, like tabs, carriage
[quoted text clipped - 13 lines]
Didn't know where to put this. Is there a way to Trim a field in a table?
Thanks! Jani

 

[Jani]

Because of time constraints I manually deleted the characters. However, this
will be a probably again next month, I'm sure, when downloading so am hanging

Try using this expression on the field to get the ascii code of the last
character in the field.

ASC(Right([TheField],1))

Then you could try
Replace([TheField],Chr(ASC(Right([TheField],1))),"")

One possibility is that the last character is a carriage return Plus a line
feed. So to get rid of that you could try the following expression
Trim(Replace([The Field],Chr(13) & Chr(10),""))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

No - it's not tab or carriage returns. Looks like superscript 0 or O's.
Perhaps you can copy the word below and see what they really are:
ADVANCE

Do they look like squares?

They might be tabs or carriage returns. Try the replace function:

Replace([Field],chr(13),"")
Replace([Filed],chr(10),"")

Jani wrote:
It isn't spaces - but can't tell what they are. Now what?

It is possible that those fields aren't padded with spaces, as they appear to
be. They could be padded with special characters, like tabs, carriage
[quoted text clipped - 13 lines]
Didn't know where to put this. Is there a way to Trim a field in a table?
Thanks! Jani

 

[Jani]

Thanks, tkelley, for trying to figure this out. I'll be trying what Spencer
suggested next month. jms

I'm not smart enough to know what they are. Maybe someone else can chime in.

In the meantime, poke around with the code on this site. It is a pain, but
it may be your only option:

http://www.visualbasicscript.com/m_26939/tm.htm

No - it's not tab or carriage returns. Looks like superscript 0 or O's.
Perhaps you can copy the word below and see what they really are:
ADVANCE

Do they look like squares?

[quoted text clipped - 10 lines]

Didn't know where to put this. Is there a way to Trim a field in a table?
Thanks! Jani

 

[Jani]

Thanks - one more thing to try next month end. jms

Good stuff from John Spencer.

Also, I'm not sure ... but when I tried to paste the word with the trailing
characters into a field in a database of my own, it truncated it and left it
just as the word you desired.

Have you tried doing a make table query using your original table to paste
all of the records into a new table to see if that action does the same?

Try using this expression on the field to get the ascii code of the last
character in the field.

ASC(Right([TheField],1))

Then you could try
Replace([TheField],Chr(ASC(Right([TheField],1))),"")

One possibility is that the last character is a carriage return Plus a line
feed. So to get rid of that you could try the following expression
Trim(Replace([The Field],Chr(13) & Chr(10),""))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

No - it's not tab or carriage returns. Looks like superscript 0 or O's.
Perhaps you can copy the word below and see what they really are:

[quoted text clipped - 14 lines]

Didn't know where to put this. Is there a way to Trim a field in a table?
Thanks! Jani