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.

 

[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.