Trim spaces between words

[BrockLanders]

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]

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]

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

'Will replace every space with ... nothing
str = Replace(str, " ", "")

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

'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 = "" Then
        Replace = ""
    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]

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