Trim spaces from textbox 3

[judgehopkins]

strTemp = LTrim(RTrim(strTemp))

I have a text box control on a form where users enter a street address into. In the after update event procedure, I want to trim any leading and following spaces from the address. It would be nice to have double spaces removed also.

Thus, " 123 Main Street " would become "123 Main Street".

I know I have to use code something like the above, but I am not sure how to do it.

Suggestions?

Thanks!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[missinglinq]

When Microsoft turned QuickBasic 4.5 into Visual Basic 1.0 they added a third trimming function, just plain Trim, which trims spaces from both ends of a string. Thus

Trim(strTemp)

does the same thing as

LTrim(RTrim(strTemp)).

If your textbox were called txtAddress then txtAddress = Trim(txtAddress) in the After Update property for the textbox will work fine.

Hope this helps.

The Missinglinq

"It's got to be the going,
not the getting there that's good!"
-Harry Chapin

 

[judgehopkins]

Private Sub PropertyAddress_AfterUpdate()
    Me.PropertyAddress = Trim([PropertyAddress])
'trims leading and following spaces from entry into control
End Sub

Thanks! The code above works.



It turns " 123 Main Street "
into "123 Main Street"
but how do I get rid of those interior spaces so it turns it into "123 Main Street"?



Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[judgehopkins]

I'm just never satisfied. The code below takes care of the double spaces, but it does not get rid of TRIPLE spaces!

Any suggestions? Anyone?

Private Sub PropertyAddress_AfterUpdate()
    Me.PropertyAddress = Trim([PropertyAddress])
'trims leading and following spaces from entry into control
    Me.PropertyAddress = Replace([PropertyAddress], _
    "  ", " ", 1, -1, vbTextCompare)
'trims double spaces from address
End Sub

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[tonyflavell]

Hi

The following code does more than you require but you may be able to modify it to filter anything out of a string. I use it as a matter of course in all Name and Address fields
in the "After Update" event.

Tony

'==================================================
Function Capstring(X)
'==================================================
'
' works on multi-word and hyphenated strings
' and removes user punctuation
'
' checks for ">" to force next char to lowercase
' checks for &quot;<&quot; to force next char to uppercase
'
' checks for hyphen, apostrophe, &quot;mc&quot; and &quot;mac&quot;
' to force next char to uppercase but can be
' modified by &quot;>&quot; (eg &quot;mac>hin&quot; will process as &quot;Machin&quot
'
' checks for space in multi-word strings
'
'==================================================
Dim Temp1, Temp2 As String, I, J, p1, p2 As Integer, b As Variant

If IsNothing(X) Then
Capstring = Null
Exit Function
End If

Temp1 = Trim(LCase(X))
Temp2 = &quot;&quot;
J = 0

'SCAN INPUT STRING AND FILTER RUBBISH

'On Error Resume Next

For I = 1 To Len(Temp1)
b = Mid$(Temp1, I, 1) 'get ANSI value of the character and
Select Case b 'filter out anything but the following:-
Case &quot;0&quot; To &quot;9&quot;, &quot;a&quot; To &quot;z&quot;, &quot; &quot;, &quot;'&quot;, &quot;-&quot;, &quot;<&quot;, &quot;>&quot;, &quot;&&quot;, &quot;(&quot;, &quot&quot;, &quot;/&quot;, &quot;#&quot; 'numeric, lcase alpha, space, apostrophe,
Temp2 = Temp2 & b 'hyphen, greater than, less than, ampersand,
J = J + 1 'hash
Case Else
End Select
Next I 'Temp2 is filtered string, j is its length

'CAPITALIZE THE FIRST LETTER AND LOOKS FOR &quot;mac&quot; AND &quot;mc&quot; AS FIRST IN THE STRING

If left$(Temp2, 2) = &quot;mc&quot; Then
Temp2 = UCase(left$(Temp2, 1)) & Mid$(Temp2, 2, 1) & UCase(Mid$(Temp2, 3, 1)) & Mid$(Temp2, 4)
ElseIf left$(Temp2, 3) = &quot;mac&quot; Then
Temp2 = UCase(left$(Temp2, 1)) & Mid$(Temp2, 2, 2) & UCase(Mid$(Temp2, 4, 1)) & Mid$(Temp2, 5)
Else
Temp2 = UCase(left$(Temp2, 1)) & Mid$(Temp2, 2)
End If

'NOW LOOK FOR &quot; mc&quot; AND &quot; mac&quot; IN THE REST OF THE STRING

p1 = InStr(Temp2, &quot; mac&quot
p2 = InStr(Temp2, &quot; mc&quot

If p1 <> 0 Then
Temp2 = left$(Temp2, p1 + 3) & UCase(Mid$(Temp2, p1 + 4, 1)) & Mid$(Temp2, p1 + 5)
ElseIf p2 <> 0 Then
Temp2 = left$(Temp2, p2 + 2) & UCase(Mid$(Temp2, p2 + 3, 1)) & Mid$(Temp2, p2 + 4)
End If

'NOW PROCESS THE ARRAY ONE CHARACTER AT A TIME

For I = 1 To J
b = Mid$(Temp2, I, 1)
Select Case b
Case Is = &quot;'&quot;, &quot; &quot;, &quot;-&quot;, &quot;/&quot;, &quot;(&quot;
Temp2 = left$(Temp2, I) & UCase(Mid$(Temp2, I + 1, 1)) & Mid$(Temp2, I + 2)
Case Is = &quot;>&quot;
Temp2 = left$(Temp2, I - 1) & LCase(Mid$(Temp2, I + 1, 1)) & Mid$(Temp2, I + 2)
J = J - 1
Case Is = &quot;<&quot;
Temp2 = left$(Temp2, I - 1) & UCase(Mid$(Temp2, I + 1, 1)) & Mid$(Temp2, I + 2)
J = J - 1
End Select
Next I

Capstring = Temp2

End Function

 

[judgehopkins]

EXCEPTIONAL!





Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[TonyJollans]

Hi Judge,

Tony's code looks jolly good if that's what you want but if all you want is to compress multiple spaces then just add a little loop to the code you posted yourself:

Private Sub PropertyAddress_AfterUpdate()
    Me.PropertyAddress = Trim([PropertyAddress])
'trims leading and following spaces from entry into control

Do While Instr([PropertyAddress], "  ") > 0

        Me.PropertyAddress = Replace([PropertyAddress], _
        "  ", " ", 1, -1, vbTextCompare)

Loop

'trims double spaces from address
End Sub

Enjoy,
Tony

 

[judgehopkins]

Hey, Tony!

Actually, your code looks jolly good!

Or, as we say in the Ozarks, &quot;Purty as a ] in moonlight!&quot;



Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[tonyflavell]

TonyJollans: - Yep! you're quite correct

judgehopkins:- Thank you kind sir!

Tony