Unanswered: How to remove embedded blanks in a string
I have a (hopefully) quick question. Is there a function in access that will allow me to change all multiple embedded blanks to a single blank. For example, I have a string that contains
"now is the time for"
and I want to change it to
"now is the time for".
Oracle provides the replace function that will allow this, but I can't find any reference in access to do the same thing. I know that I could write a loop in the code and parse out the strings, but I would rather use a function.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
You'll have to roll your own in that case. 2k+ has much better string utilities.
Here's an example:
Function Replace(strExpr As String, strFind As String, strReplace As String,
Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long
lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)
If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
lng = lngStart
If lng > 1 Then
strOut = Left$(strExpr, lng - 1)
Do While lng <= lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
Replace = strOut