Trim spaces between words

BrockLanders · Mar 5, 2003

Is there a way to remove not only leading and trailing spaces of text strings (Trim), but spaces in between words as well. I wanted to include it in the criteria of a field for a query.

Thanks

lameid · Mar 5, 2003

You'll have to right your own function to remove the spaces...

The following text funtions may be involved (at least the first three).

Instr
Mid
len
right
left

Any reason that you can't use the Like operator and wildcards for your comparison?

psemianonymous · Mar 5, 2003

If you want to remove all instances of space, use the Replace function:

Code:

'Will replace every space with ... nothing
str = Replace(str, &quot; &quot;, &quot;&quot;)

With newer versions of Access it should be built in, but with Access 97 you'll have to make your own. Like so:

Code:

'Replace()
'
'Replaces any substring with any other substring.

Public Function Replace(ByRef originalString As String, ByRef textToReplace As String, _
                        ByRef replacementText As String)
    Dim offset As Integer
    
    If originalString = &quot;&quot; Then
        Replace = &quot;&quot;
    Else
        offset = InStr(1, originalString, textToReplace, vbTextCompare)
        
        If offset <= 0 Then
            Replace = originalString
        Else
            Replace = Replace(Left(originalString, offset - 1), textToReplace, replacementText) & _
                      replacementText & _
                      Replace(Mid(originalString, offset + Len(textToReplace)), textToReplace, replacementText)
        End If
    End If
End Function

BrockLanders · Mar 6, 2003

I am using Access 2000 and was not even aware of the Replace function. Thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Trim spaces between words

BrockLanders

Programmer

lameid

Programmer

psemianonymous

Programmer

BrockLanders

Programmer

Similar threads

Part and Inventory Search

Sponsor