Find And Replace Text String VBA

[Guest]

Does anyone have any code on how to replace specified text string within a
text string? I am trying to eliminate all spaces within a Address field , ex
123 SAINT JOSEPH CT. I have a update query that I created that will give me
the desired result, but I have to run it multiple times to replace all the
spaces. I am using a InStr function to locate the Start position and then I
concatenate the left and Mid portions (minus the space) to get the desired
results. I have done a LTrim if the location =1 and a criteria <>0. How
could I run the query multiple times, please give an example of some code?

The update query, "UPDATE ADDRESS QUERY" has a select query linked to the
actual "ADDRESS" table. The select query, "ADDRESS TEXT LOCATION" has a
field "LOCATION' with a location value of the start of the text. If the loop
could stop with the Sum of [ADDRESS TEXT LOCATION]![LOCATION] =0, that would
be great. Thanks in advance.

 

[Guest]

Also, I am running Access 2000.

 

[Ken Snell [MVP]]

Public Function RemoveDoubleSpaces(ByVal strOriginalValue) As String
Do While InStr(strOriginalValue, " ") > 0
strOriginalValue = Replace(strOriginalValue, " ", " ", 1, -1,
vbTextCompare)
Loop
RemoveDoubleSpaces = strOriginalValue
End Function

--

Ken Snell
<MS ACCESS MVP>

Also, I am running Access 2000.

Does anyone have any code on how to replace specified text string within a
text string? I am trying to eliminate all spaces within a Address field , ex
123 SAINT JOSEPH CT. I have a update query that I created that will give me
the desired result, but I have to run it multiple times to replace all the
spaces. I am using a InStr function to locate the Start position and then I
concatenate the left and Mid portions (minus the space) to get the desired
results. I have done a LTrim if the location =1 and a criteria <>0. How
could I run the query multiple times, please give an example of some code?

The update query, "UPDATE ADDRESS QUERY" has a select query linked to the
actual "ADDRESS" table. The select query, "ADDRESS TEXT LOCATION" has a
field "LOCATION' with a location value of the start of the text. If the loop
could stop with the Sum of [ADDRESS TEXT LOCATION]![LOCATION] =0, that would
be great. Thanks in advance.

 

[Guest]

This might be a stupid question, but given that the Table name is ADDRESS and
the Field name is STREETNAME. Would I need to place the information
somewhere in the code. I'm sure it relates to the strOriginalValue portion,
but I really dont know the proper way to edit the below code, can anyone
help? Thanks in advance.

Public Function RemoveDoubleSpaces(ByVal strOriginalValue) As String
Do While InStr(strOriginalValue, " ") > 0
strOriginalValue = Replace(strOriginalValue, " ", " ", 1, -1,
vbTextCompare)
Loop
RemoveDoubleSpaces = strOriginalValue
End Function

--

Ken Snell
<MS ACCESS MVP>

Also, I am running Access 2000.

Does anyone have any code on how to replace specified text string within a
text string? I am trying to eliminate all spaces within a Address field , ex
123 SAINT JOSEPH CT. I have a update query that I created that will give me
the desired result, but I have to run it multiple times to replace all the
spaces. I am using a InStr function to locate the Start position and then I
concatenate the left and Mid portions (minus the space) to get the desired
results. I have done a LTrim if the location =1 and a criteria <>0. How
could I run the query multiple times, please give an example of some code?

The update query, "UPDATE ADDRESS QUERY" has a select query linked to the
actual "ADDRESS" table. The select query, "ADDRESS TEXT LOCATION" has a
field "LOCATION' with a location value of the start of the text. If the loop
could stop with the Sum of [ADDRESS TEXT LOCATION]![LOCATION] =0, that would
be great. Thanks in advance.

 

[Ken Snell [MVP]]

This function can be put in a regular module, and you can call it from a
query using a calculated field:

MyTrimmedString: RemoveDoubleSpaces([StreetName])

This can be used in a select query.

However, if you're wanting to run an update query to change the field's
value, this query should work:

UPDATE Address
SET StreetName = RemoveDoubleSpaces([StreetName]);

You then would just need to run the query once to get rid of all double
spaces.

--

Ken Snell
<MS ACCESS MVP>

This might be a stupid question, but given that the Table name is ADDRESS and
the Field name is STREETNAME. Would I need to place the information
somewhere in the code. I'm sure it relates to the strOriginalValue portion,
but I really dont know the proper way to edit the below code, can anyone
help? Thanks in advance.

Public Function RemoveDoubleSpaces(ByVal strOriginalValue) As String
Do While InStr(strOriginalValue, " ") > 0
strOriginalValue = Replace(strOriginalValue, " ", " ", 1, -1,
vbTextCompare)
Loop
RemoveDoubleSpaces = strOriginalValue
End Function

--

Ken Snell
<MS ACCESS MVP>

Also, I am running Access 2000.

:

Does anyone have any code on how to replace specified text string

within
a

text string? I am trying to eliminate all spaces within a Address

field
, ex

123 SAINT JOSEPH CT. I have a update query that I created that will give me
the desired result, but I have to run it multiple times to replace

all
the

spaces. I am using a InStr function to locate the Start position

and
then I

concatenate the left and Mid portions (minus the space) to get the desired
results. I have done a LTrim if the location =1 and a criteria <>0. How
could I run the query multiple times, please give an example of some code?

The update query, "UPDATE ADDRESS QUERY" has a select query linked

to
the

actual "ADDRESS" table. The select query, "ADDRESS TEXT LOCATION" has a
field "LOCATION' with a location value of the start of the text. If

the
loop

could stop with the Sum of [ADDRESS TEXT LOCATION]![LOCATION] =0,

that
would

be great. Thanks in advance.