What's the easiest way to change a sentence consisting of 3-7 words into a string in which the spaces have been replaced with the plus sign (+) ?

[MLH]

Suppose I have a sentence like "One Two Three Four"
and I want it to say "One+Two+Three+ Four". Taking
into account that there might be more than a single
space separating the words sometimes.

Is this fairly easy to accomplish within code?

[MLH]

I found this code...

Do While (InStr(strSourc e, " "))
' if true, the string still contains double spaces,
' replace with single space
strSource = Replace(strSour ce, " ", " ")
Loop

But I can't find information on the Replace
command. Is there one?

[teddysnips]

Depending on the version of Access you have, you might care to look up
the "Replace" function:

Returns a string in which a specified substring has been replaced with
another substring a specified number of times.

Syntax

Replace(express ion, find, replace[, start[, count[, compare]]])

In your case:

Replace("One Two Three Four", "", "+")

Otherwise you'll have to do what we all did in the bad old days and
roll your own using the "Mid" or "InStr" functions. Not difficult.
Just boring.

Edward

[MLH]

I'm starting to envy others having that
FN capability. Unfortunately, it does
not appear to be in Access 97.

[fredg]

On Mon, 23 May 2005 11:56:04 -0400, MLH wrote:

I'm starting to envy others having that
FN capability. Unfortunately, it does
not appear to be in Access 97.

You can use the following User Defined function in any version of
Access. It will remove all spaces between words and add the + sign.

Copy the following code to a module:

Public Function InsertPlus(strI n As String) As String

Dim intX As Integer
Dim strNew As String
Dim strNext As String
strNext = strIn
intX = InStr(strIn, " ")
Do While intX <> 0
strNew = strNew & Left(strNext, intX - 1) & "+"
strNext = LTrim(Mid(strNe xt, intX))
intX = InStr(strNext, " ")
Loop
strNew = strNew & strNext
InsertPlus = strNew

End Function

Call it from a query:
NewText:InsertP lus([FieldName])

Filter out Nulls and add error handling as needed.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Chuck Grimsby]

Pop this code (from Joe Foster) into a module somewhere. It works
perfectly, and some people have reported it's even faster then VBA's
own Replace function. (I find myself still using it in A2K3
applications!)

Public Function JoeReplace(ByVa l Expression As String, _
ByVal Find As String, _
ByVal Repl As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = -1 _
) As String

If Start < 1 Or Count < -1 Then
Err.Raise 5
ElseIf Start > Len(Expression) Then
' nothing to do!
Exit Function
ElseIf Len(Find) < 1 Or Count = 0 Then
If Start <> 1 Then JoeReplace = Mid$(Expression , Start) _
Else JoeReplace = Expression
Exit Function
End If

Dim p As Long, Q As Long, rlen As Long
p = Start
Do
Q = InStr(p, Expression, Find) ', jCompare)
If Q = 0 Then Exit Do

If rlen + Q - p + Len(Repl) > Len(JoeReplace) Then
JoeReplace = JoeReplace & JoeReplace & Space$(Q - p + Len(Repl))
End If

If p < Q Then Mid$(JoeReplace , rlen + 1) = Mid$(Expression , p, Q -
p)
If Len(Repl) Then Mid$(JoeReplace , rlen + Q - p + 1) = Repl

Count = Count - 1
rlen = rlen + Q - p + Len(Repl)
p = Q + Len(Find)
Loop Until Count = 0
JoeReplace = Left$(JoeReplac e, rlen) & Mid$(Expression , p)
End Function