Removing excel carriage returns from access tex/memo field

  • Thread starter Thread starter RRH
  • Start date Start date
R

RRH

I'm trying to use the Microsoft knowledge base article
115576 and it is not working. CAN someone please help. Is
the sample Function and query update in the article
correct.

I have alot of text with this promblem.
 
Well, it's terrible VBA code, but it should do what it says it does.

What happens when you use it? "It is not working" doesn't give us much to go
by!

And what version of Access are you using? If you're using Access 2000 or
newer, you should be able to use the Replace function instead of the
ChangeStr function in that article.
 
rrh said:
Access 97

It erases everything in the text field.

I followed the article to the letter. Do I need to modify
and how. with what.
Click to expand...
PMFBI

I have to wonder if your Excel "carriage return"
is Chr(13) & Chr(10), or only Chr(13),
or only Chr(10)?

Is it possible you are only replacing one
or the other of CR LF? If that was the
case, I could imagine "not seeing anything"
until I made the row taller if you only
replaced CR.


In 97 I always used the following function
from Viktor Umanskiy:

Function MyReplace(Orig As String, Find As String, Repl As String) As String
Dim k As Long, L1 As Long, M As Long
MyReplace = ""
L1 = Len(Find) - 1
M = 1
k = InStr(Orig, Find)
Do While k > 0
MyReplace = MyReplace & Mid(Orig, M, k - 1) & Repl
M = M + k + L1
k = InStr(Mid(Orig, M), Find)
Loop
MyReplace = MyReplace & Mid(Orig, M)
End Function

Save in a module, then update query would be like:

UPDATE YourTable
SET [yourfield] = MyReplace([yourField], Chr(13) & Chr(10), "")

Again, apologies for butting in.

Good luck,

Gary Walter
 
Back
Top