Remove LF or CR from end of line.

[pekajo]

Hi,
There is something at the end of my fields (but only some so I cannot use mid ()) and it's not a space as was thinking it maybe a LF and/or CR.
Can anyone tell me how to remove LF or CR from end of line with vba.
Thanks
Peter

 

[June7]

Can use Replace() function but quite possible there is some other non-printing character. Or try Left(x, Len(x)-1). Oops, you said only some so some records might not have this issue?

See if you can determine character code of last character in a query: Asc(Right(fieldname,1)). CR is 13, LF is 10

 

[Galaxiom]

Replace(whatever, Chr(13),"")

 

[pekajo]

Did a ASC check as above and found it was a 160 and according to Google its 'ASCII 160 (non-breaking space), whatever that is'!!!

 

[Galaxiom]

Did a ASC check as above and found it was a 160 and according to Google its 'ASCII 160 (non-breaking space), whatever that is'!!!

It is a space that will not cause the text to wrap at it like an ordinary space does.

 

[June7]

Interesting, Access drops normal trailing spaces but is retaining this non-breaking space. I would never have guessed. Are there other non-breaking spaces within the data? If not, just do Replace() to empty string. If there are, try doing a Replace() to change to normal space (32) and see if Access drops trailing space.

Where is data coming from?

 

[The_Doc_Man]

That would imply that the space was created by another program perhaps like Word, because the non-breaking space is not one of the characters normally associated with Access. And it doesn't sound familiar as an Excel output either.

However, the "Replace" function would allow you to use it similarly to Galaxiom's post #3. Instead of 13, use 160.

 

[Gasman]

I thought it was a HTML code? &nbsp

 

[The_Doc_Man]

That could also explain it. We didn't get an explanation of the source of this text, after all. But a screen capture would make sense.