Removing trailing paragraph marks from data

C

Charlie

Is there a way to remove any trailing paragraphh marks from a memo field in
my Access Database so the won't leaves lots of blank lines in my reports?
Thanks, charlie
 
J

John Spencer (MVP)

Yes there is.

To do just the last one, you could use an update query

UPDATE YourTable
SET YourField = Left(YourField,Len(YourField)-2)
WHERE YourField Like "*" & Chr(13) & Chr(10)

That will remove the last carriage return, line feed combination if they are the
last two characters in the field.

Rinse and repeat unitl you don't have any more records to update.

Another way would be to write a custom function to do this. UNTESTED AIRCODE follows

Public Sub StripCRLF(strIn as String)
Dim iLoop as Integer, iLong as Integer

iLong = Len(strIn)
For iLoop = iLong to 2 Step -1
If Mid(strIn,Iloop,1) = chr(13) or Mid(strIn,Iloop,1) = Chr(10) Then
StrIN = Left(strIN,Len(strIn)-1)
Else
Exit For
End If
Next iLoop

Save that in a module and then use it in your SQL

UPDATE YourTable
SET YourField = StripCrLF(YourField)
WHERE YourField Like "*" & Chr(13) & Chr(10)

End Sub
 
Top