Access equivalent of str_replace() function

Hello,

Is there an Access equivalent of the str_replace() function?

Here's my dilemma:

In an Access '97 table, I have a text field containing item descriptions.
The process that imported the data from another database has inserted a
"~;~" (minus the quotes) in random locations within most of the descriptions
(with multiple instances of the insertion in a few of the descriptions).

I'd like to run a query from that data, removing the "~;~" or replacing it
with spaces.

Is there a way to do that?

Sincerely,
Don Hicks
Portland, Oregon

Hi Don

Here's a function that I've been using..


Public Function ReplaceString(ByVal varCurrentValue, strOld As String, strNew
As String)

Dim strNewText As String
Dim intStart As Integer, intRight As Integer

If Len(Nz(varCurrentValue, "")) 0 Then
' Get position of first strOld to start loop
intStart = InStr(varCurrentValue, strOld)
If intStart = 0 Then
ReplaceString = varCurrentValue
Else
' Loop until no more strOlds found in current field
Do Until intStart = 0
' Get position of first strOld
intStart = InStr(varCurrentValue, strOld)
' Get end position of strOld
intRight = intStart + Len(strOld)
' Set variable for new text to characters to left
' and right of strOld, inserting strNew
' in place of strOld
strNewText = Left(varCurrentValue, intStart - 1) & strNew &
Mid(varCurrentValue, intRight)

varCurrentValue = strNewText
' Get position of next strOld
intStart = InStr(varCurrentValue, strOld)
Loop
ReplaceString = strNewText
End If
End If

Hope this helps

Best regards

Maurice St-Cyr
Micro Systems Consultants, Inc



Hello,

Is there an Access equivalent of the str_replace() function?

Here's my dilemma:

In an Access '97 table, I have a text field containing item descriptions.
The process that imported the data from another database has inserted a
"~;~" (minus the quotes) in random locations within most of the descriptions
(with multiple instances of the insertion in a few of the descriptions).

I'd like to run a query from that data, removing the "~;~" or replacing it
with spaces.

Is there a way to do that?

Sincerely,
Don Hicks
Portland, Oregon

Have you considered opening the table in datasheet view and selecting
Edit|Replace? This is quite handy if you only have to do this once.

--
Duane Hookom
MS Access MVP
--

"Don Hicks" wrote in message
...
Hello,

Is there an Access equivalent of the str_replace() function?

Here's my dilemma:

In an Access '97 table, I have a text field containing item descriptions.
The process that imported the data from another database has inserted a
"~;~" (minus the quotes) in random locations within most of the
descriptions
(with multiple instances of the insertion in a few of the descriptions).

I'd like to run a query from that data, removing the "~;~" or replacing it
with spaces.

Is there a way to do that?

Sincerely,
Don Hicks
Portland, Oregon

Hey Duane,

I don't know about the tilde-semicolon-tilde thingie, but the
Edit/Replace doesn't work very well in A97 (SR2). At least, it doesn't
"see" the special characters ("The search item was not found.") See my
reply to AZEye, "Removing a "." Update query".

Cheers,

LeAnne

Duane Hookom wrote:

Have you considered opening the table in datasheet view and selecting
Edit|Replace? This is quite handy if you only have to do this once.

--
Duane Hookom
MS Access MVP
--

"Don Hicks" wrote in message
...
Hello,

Is there an Access equivalent of the str_replace() function?

Here's my dilemma:

In an Access '97 table, I have a text field containing item descriptions.
The process that imported the data from another database has inserted a
"~;~" (minus the quotes) in random locations within most of the
descriptions
(with multiple instances of the insertion in a few of the descriptions).

I'd like to run a query from that data, removing the "~;~" or replacing it
with spaces.

Is there a way to do that?

Sincerely,
Don Hicks
Portland, Oregon