Hi SuzyQ,
there is code you can run on the after update event of a text box to remove
leading spaces and other unwanted characters. I often do this.
Here is sample code, tweak it to suit your requirements.
------------------------------------------------Public Function
StripSpecialChars(strSource As String) As String
' Inputs: strSource from textbox on form
' Output: string containing only acceptable characters
' excludes "", line breaks, | pipe character, tabs, extra white space
'Purpose:
' Use: call from after update event of control
If Len(strSource & vbNullString) > 0 Then
strSource = Trim$(String:=strSource)
'Debug.Print strSource
strSource = Replace(expression:=strSource, Find:=Chr(13) & Chr(10),
Replace:=Chr(32))
strSource = Replace(expression:=strSource, Find:="""", Replace:="")
'Debug.Print strSource
If FindWordXlSpecialChars(strTextIn:=strSource) Then
strSource = StripWordXlSpecialChars(strTextIn:=strSource)
End If
'Debug.Print strSource
'remove extra white space
strSource = TrimSpace(strInput:=strSource)
'Debug.Print strSource
End If
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
-------------------------------
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
SuzyQ said:
I have a check for data in a field in order to determine what to display on
a
form.
I'm using the code like this
if not isnull([textboxcontrol]) then
'display information
else
'don't display information
endif
however a field may have a space or other not printable character and it
is
not considered null - I don't want to display fields if the field is blank
(any form of blankness) how can I do this?
