TRIM CR and LF

[david epsom dot com dot au]

I realised, after pasting some text into a textbox, that Access does not
trim CR and LF from pasted text.

What would be the best (cleanest) way to trim text so that it does not
include control characters? Specifically, so that text is cleared of stray
CR/LF at the end of a text value, or any non-text at the end, or any
non-text anywhere?


(david)

 

[Ken Snell]

Use control's AfterUpdate event to run a function that uses the Replace
function to remove all characters that you don't want.

Private Sub txtBoxName_AfterUpdate()
Me.txtBoxName.Value = DeleteUndesirables(Me.txtBoxName.Value)
End Sub


Public Function DeleteUndesirables(strString As String) As String
Dim strWorkString As String
strWorkString = strString
strWorkString = Replace(strWorkString, Chr(13), "", 1, -1,
vbTextCompare)
strWorkString = Replace(strWorkString, Chr(10), "", 1, -1,
vbTextCompare)
' continue for all other characters to be deleted
DeleteUndesirables = strWorkingString
End Function

Alternatively, you could loop through the string in the function and compare
each character to the alphanumeric character set that you want to allow, and
delete a character if it's undesirable. But that may be more looping than is
necessary.

 

[Brendan Reynolds]

Control characters (carriage return, line feed, tab, etc.) all have ASCII
codes less than 32 (the ASCII code for a space) so ...

Public Function StripControlCharacters(ByVal strStringToStrip As String) As
String

Dim lngCounter As Long
Dim strWork As String
Dim strChar As String
Dim intChar As Integer

For lngCounter = 1 To Len(strStringToStrip)
strChar = Mid$(strStringToStrip, lngCounter, 1)
intChar = Asc(strChar)
If intChar >= 32 Then
strWork = strWork & strChar
End If
Next lngCounter

StripControlCharacters = strWork

End Function

Example of use, in Immediate window ...

? stripcontrolcharacters("some" & vbcrlf & "text")
sometext