How do I remove carriage returns from an Access file?


WARNING: Always work on a COPY of your source data and NOT on the original data when carrying out this type of modification.

Create a function in Access as follows;
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)
MyReplace = MyReplace & Mid(Orig, M)
End Function
Now you can call the function for any field which needs updating via an update query as follows;
UPDATE YourTable
SET [yourfield] = MyReplace([yourField], Chr(13) & Chr(10), “”)