Remove Carraige Returns in Text fields

  • Thread starter Thread starter Vicky
  • Start date Start date
V

Vicky

I have a database and in some of the address fields the
users have entered a carraige return, then entered the
same data again. Is there a way I can remove the carraige
returns via a query, formatting or find and replace.
There are circa 25,000 records so manually checking is not
a preferred option.

thanks
 
The combination of CR and LF characters is a "carriage return" in ACCESS.

You can run an update query that finds this combination ( Chr(13) &
Chr(10) ) and deletes it from the field's value.

If you're using ACCESS 2002 you can use the Replace function directly in the
query's "Update To:" expression. If ACCESS 2000, depending on the Jet level
(?) you can also do this; otherwise, write a public function that you call
and that uses the Replace function to give you the desired result. If ACCESS
97, you need to use a customized function (I've pasted a function posted by
Arvin Meyer, MVP below):

Function ChangeString(strIn As String, strToFind As String, strReplace As
String, _
Optional intCount As Variant) As String
On Error Resume Next

Dim intPlace As Integer
Dim intCounter As Integer
Dim intStart As Integer

If IsMissing(intCount) Then intCount = 32767
intStart = 1

For intCounter = 1 To intCount
intPlace = InStr(intStart, strIn, strToFind)
If intPlace > 0 Then
strIn = Left(strIn, intPlace - 1) & strReplace & Mid(strIn,
intPlace + Len(strToFind))
intStart = intPlace + Len(strReplace) + 1
Else
Exit For
End If
Next

ChangeString = strIn

End Function
 
Back
Top