remove spaces

[ice-9]

Hi,

i want to replace spaces in my query, but there are a few hard parts in it.
I dont always know how many spaces. en second, the spaces that need to be replaced are after the first character. The rest needs to be removed.

i have:
----1----1----2

the "-" are spaces!!
and i want it to show
1.1.2

this is what i tried replace([MinutesID];" ";".") & " - " & [minutesTitle]

can someone assist...
thanks

 

[raskew]

Hi -

This is an A97 solution (where the Replace() function didn't exist). It will, however work in newer Access versions. Copy/paste to a new module then follow the comments to test from the debug (immediate) window.

HTH - Bob

Function onespace2(pStr As String, pDelim As String) As String
'*******************************************
'Purpose:   Removes excessive spaces from a string
'           and replaces remaining spaces with user
'           selected delimiter.
'Coded by:  raskew
'Inputs:    From debug window:
'           ? onespace2(" the    quick    brown fox", ".")
'Output:    "the.quick.brown.fox"
'*******************************************

Dim strHold As String
    strHold = RTrim(pStr)
    Do While InStr(strHold, "  ") > 0
      strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold, InStr(strHold, "  ") + 1)
    Loop
    
    strHold = Trim(strHold)
    Do While InStr(strHold, " ") > 0
      strHold = Left(strHold, InStr(strHold, " ") - 1) & pDelim & Mid(strHold, InStr(strHold, " ") + 1)
    Loop
    onespace2 = Trim(strHold)
    
End Function

 

[ice-9]

work perfectly, just the way i wanted it to work!
Thank you!