Hi MN
Trim only removes spaces. There are other characters that show as a space in
Access, but are not a space, such as chr(9), chr(10).
I have some functions that I routinely use on the after update event of some
fields. I call it like this-->
=CleanTextEntry()
I place the call on the property dialog next to the After update event.
Here is the code
---------------------------
Public Function CleanTextEntry()
' Inputs: string to clean
' Output: string with unwanted chars removed
With Screen.ActiveControl
If Len(.Value) > 0 Then
.Value = Trim$(String:=.Value)
'remove line breaks
.Value = Replace(expression:=.Value, Find:=Chr(13) & Chr(10),
Replace:=Chr(32))
'remove double quote character
.Value = Replace(expression:=.Value, Find:="""", Replace:="")
'remove other chars from paste from word or excel if present
If FindWordXlSpecialChars(strTextIn:=.Value) Then
.Value = StripWordXlSpecialChars(strTextIn:=.Value)
End If
'remove extra white space
.Value = TrimSpace(strInput:=.Value)
'Debug.Print .Value
End If
End With
End Function
Public Function FindWordXlSpecialChars(strTextIn As String) As Boolean
'Purpose:' This function checks for existence of special characters
' used in Word and Excel
' Chr(9) = tab
' Chr(10) = line feed
' Chr(11) = manual line break (shift-enter)
' Chr(12) = manual page break
' Chr(13) = carriage return
' Chr(14) = column break
' Chr(30) = non-breaking hyphen
' Chr(160) = nonbreaking space
Dim astrText() As String
Dim astrChars() As String
Dim astrMatches() As String
Dim lngCount As Long
Dim strCharacters As String
strCharacters = Chr(9) & " " & Chr(10) & " " & Chr(11) & " " _
& Chr(12) & " " & Chr(13) & " " & Chr(14) & " " & Chr(30) & " " &
Chr(160)
' Split character string into array.
astrChars() = Split(expression:=strCharacters)
' Split string into array.
astrText = Split(expression:=strTextIn)
' Check each character in passed-in string
For lngCount = LBound(astrText) To UBound(astrText)
' Filter function returns array containing matches found.
' If no matches are found, upper bound of array is less than
' lower bound. Store result returned by Filter function in a
' String array, then compare upper bound with lower bound.
astrMatches = Filter(astrChars, astrText(lngCount))
If UBound(astrMatches) < LBound(astrMatches) Then
' If no match
FindWordXlSpecialChars = False
Else
FindWordXlSpecialChars = True
Exit Function
End If
Next
' Join the string.
Join (astrText)
End Function
Private Function TrimSpace(strInput As String) As String
'Purpose: This procedure trims extra space from any part of
' a string. accepts a single word or letter without error
Dim astrInput() As String
Dim astrText() As String
Dim strElement As String
Dim lngCount As Long
Dim lngIncr As Long
' Split passed-in string.
astrInput = Split(strInput)
' Resize second array to be same size.
ReDim astrText(UBound(astrInput))
' Initialize counter variable for second array.
lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
For lngCount = LBound(astrInput) To UBound(astrInput)
strElement = astrInput(lngCount)
If Len(strElement & vbNullString) > 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
End If
Next
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)
' Join new array to return string.
TrimSpace = Join(astrText)
'Debug.Print TrimSpace
End Function
Private Function StripWordXlSpecialChars(strTextIn As String) As String
strTextIn = Replace(expression:=strTextIn, Find:="Chr(9)",
Replace:=Chr(32))
strTextIn = Replace(expression:=strTextIn, Find:="Chr(10)",
Replace:=Chr(32))
strTextIn = Replace(expression:=strTextIn, Find:="Chr(11)",
Replace:=Chr(32))
strTextIn = Replace(expression:=strTextIn, Find:="Chr(12)",
Replace:=Chr(32))
strTextIn = Replace(expression:=strTextIn, Find:="Chr(13)",
Replace:=Chr(32))
strTextIn = Replace(expression:=strTextIn, Find:="Chr(14)",
Replace:=Chr(32))
strTextIn = Replace(expression:=strTextIn, Find:="Chr(30)",
Replace:=Chr(32))
strTextIn = Replace(expression:=strTextIn, Find:="Chr(160)",
Replace:=Chr(32))
StripWordXlSpecialChars = strTextIn
End Function
