How do I trim an input field

[Dennis]

Hi,

I'm on Access 2003 on XP Pro.

I'm trying to make sure there are no extra spaces in my address fields.
I've tried

Me.txtAddr1 = trim$(Me.txtAddr1)
and
me.txtAddr1 = trim$(Address1)

in both the BeforeUpdate and AfterUpdate events of the control (txtAddr1) and
neither one removed the extra blanks. What is the correct way to remove the
extra spaces from a text input field.

 

[Mike Painter]

Hi,

I'm on Access 2003 on XP Pro.

I'm trying to make sure there are no extra spaces in my address
fields.
I've tried

Me.txtAddr1 = trim$(Me.txtAddr1)
and
me.txtAddr1 = trim$(Address1)

in both the BeforeUpdate and AfterUpdate events of the control
(txtAddr1) and neither one removed the extra blanks. What is the
correct way to remove the extra spaces from a text input field.

Where does this information come from? There may be non printing characters
in the string. I see no other reason why leading and trailing spaces would
not be removed.

Trim or Trim$ in VB will only remove trailing and leading blanks.
If you want to remove additional interior blanks you will have to write a
function or switch to Pick basic where trim( " this is a mess
") returns "This is a mess"

 

[John W. Vinson]

Hi,

I'm on Access 2003 on XP Pro.

I'm trying to make sure there are no extra spaces in my address fields.
I've tried

Me.txtAddr1 = trim$(Me.txtAddr1)
and
me.txtAddr1 = trim$(Address1)

in both the BeforeUpdate and AfterUpdate events of the control (txtAddr1) and
neither one removed the extra blanks. What is the correct way to remove the
extra spaces from a text input field.

Trim() removes only the blanks before and after the first nonblank character:
e.g.

Trim(" A text string "

will return "A text string".

If you want to replace all internal multiple blanks with a single blank,
you'll need some custom code. A brutally inefficient approach would be to
repeatedly use

Replace(string, " ", " ")

until the string quits shrinking; smarter approaches are available.

 

[Dennis]

Mike,

I thought that Access Trim worked like the Pick Trim, but obviously I was
wrong. Rats. Pick sure makes life easy.

 

[Dennis]

Johh,

What would be a smarter approach? I can see taking the length of the string
and and looping through the string to removing the extra spaces or to build
another string with the spaces removed.

Is that a beter way?

 

[Mike Painter]

Mike,

I thought that Access Trim worked like the Pick Trim, but obviously I
was wrong. Rats. Pick sure makes life easy.

They finally got Split and Access 7 sort of uses multivalued fields but
that's about it.

I miss a lot of the fuctions but my biggest gripe is that they don't expose
teh contents of the last record. It's there because a CTRL- quote gets it
but they don't have a Me.Myfield.lastvalue

 

[John W. Vinson]

Johh,

What would be a smarter approach? I can see taking the length of the string
and and looping through the string to removing the extra spaces or to build
another string with the spaces removed.

Is that a beter way?

Well, removing all the spaces would be easy, butyouwouldnotliketheresult!

It's not that hard though:

Public Function PickTrim(strIn As String) As String
Dim i As Integer
Dim InBl As Boolean
Dim strCh As String
' trim leading and trailing
strIn = Trim(strIn)
InBl = False
For i = 1 To Len(strIn)
strCh = Mid(strIn, i, 1)
If strCh = " " Then
If Not InBl Then
PickTrim = PickTrim & strCh
End If
InBl = True
Else
InBl = False
PickTrim = PickTrim & strCh
End If
Next i
End Function

 

[Dennis]

John, Mike,

I'm still learning Access so I'm trying to learn how to write efficient
Access code. I would appreciate it if you could comment on which method is
more efficient.

I think the first is more efficient because it makes less loops, but I don't
know how costly the InStr function is.

The second method does character by character manipulation.

Any comments would be greatly appreceiated.

=========================================

Function PickTrim(strInput As String) As String

Dim strOut As String
Dim intSpPos As Integer

strOut = Trim(strInput)
intSpPos = InStr(strOut, " ")
Do While intSpPos > 0
strOut = Left(strOut, intSpPos - 1) & Mid(strOut, intSpPos + 1)
intSpPos = InStr(strOut, " ")
Loop
PickTrim = strOut

End Function


=========================================


Function TrimSpaces(strInput) As String
Dim strCharRemove As String ' assigns
the char to look for & replace
Dim strCurChar As String ' Current
character to match up
Dim intNoChars As Integer ' Number of
characters in string
Dim x As Integer ' count of
chars in string
Dim intSpCt As Integer ' # of
spaces in a row.

strCharRemove = " " ' looks for
a space (can be any character)
intSpCt = 0

' trim spaces between text

strInput = Trim$(strInput)
intNoChars = Len(strInput) ' Get the
length of the string
For x = 1 To intNoChars
strCurChar = Mid(strInput, x, 1) ' Get curr
char from original string

If strCurChar = " " Then ' if the
character matches a space
intSpCt = intSpCt + 1
Else
intSpCt = 0 ' Reset flag
when find first non-matching character
End If
If intSpCt <= 1 Then
TrimSpaces = TrimSpaces & strCurChar ' create
parsed string
End If
' Debug.Print intNoChars, x, strCurChar, intSpCt, strInput, TrimSpaces
Next x

 

[Dennis]

Johh,

After I though about it, I came up with an approach very similar to your.
Very cool, I guess I'm learning Access after all.

Thank you for your assitance.

 

[Clifford Bass via AccessMonster.com]

Hi Dennis,

Here is another replacement function for Trim() that deals with null
values, considers tabs and spaces to be equivalent, removes trailing and
leading CR/LFs, and condenses internal multiple CR/LF sequences to single
CR/LFs.

Public Function TrimIt(ByVal varValue As Variant) As Variant

Dim intIndex As Integer
Dim strCurrentWord As String
Dim strPreviousWord As String
Dim strReturn As String
Dim strarrWords() As String

If IsNull(varValue) Then
TrimIt = Null
Else
strPreviousWord = vbCrLf
strReturn = ""
strarrWords = Split(Replace(Replace(varValue, _
vbTab, " "), vbCrLf, " " & vbCrLf))
For intIndex = LBound(strarrWords) To _
UBound(strarrWords)
strCurrentWord = strarrWords(intIndex)
If strCurrentWord <> "" Then
If strReturn <> "" And strPreviousWord <> _
vbCrLf And strCurrentWord <> vbCrLf Then
strReturn = strReturn & " "
End If
If strPreviousWord <> vbCrLf Or strCurrentWord _
<> vbCrLf Then
strReturn = strReturn & strCurrentWord
End If
strPreviousWord = strCurrentWord
End If
Next intIndex
If strReturn <> "" And strPreviousWord = vbCrLf Then
strReturn = Left(strReturn, Len(strReturn) - 2)
End If
TrimIt = strReturn
End If

End Function

It is possible that the code may need tweaking to deal with situations I
have missed.

Clifford Bass

 

[Mike Painter]

I would use Replace(StrIn," ", " ') , loop through the string, and bet a
nickel that if you did this with 10,000 strings it would be faster than
doing any other method 10,000 times by at least a second, maybe two.
I assume that Access searches for the first string in an efficent manner and
that means it will be faster than a character by character search.
Since it is fairly rare to have more than one space between words, etc it
will be making one pass most of the time.