Replace function

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

Hi,

I am trying to use code to REPLACE for a string of text.
I want to use either SQL or VB. I do know that it looks
something like: Replace (string, search string, replace
string)

This is what I want to do: in Field12 of table Cats,
replace the word 'dogs' with blank.

In Field12

of table Cats

Replace (Like *dogs*, 'dogs', '')
 
Hi Jan

You can do this with an update query. Something like this:

Update Cats set Field12=Replace(Field12, 'dogs', '') where Field12 like
'*dogs*';

If you are running an earlier version of Access 2000 then Replace will work
in VBA but not in SQL. You will need to write your own wrapper function:

Public Function SqlReplace (strOrig As String, strFind As String, _
strNew As String) As String
SqlReplace = Replace(strOrig, strFind, strNew)
EndFunction

Use SqlReplace instead of Replace in your query.
 
I just wanted to let you know the query worked and I have
been able to adapt it to several uses!

-----Original Message-----
Hi Jan

You can do this with an update query. Something like this:

Update Cats set Field12=Replace(Field12, 'dogs', '') where Field12 like
'*dogs*';

If you are running an earlier version of Access 2000 then Replace will work
in VBA but not in SQL. You will need to write your own wrapper function:

Public Function SqlReplace (strOrig As String, strFind As String, _
strNew As String) As String
SqlReplace = Replace(strOrig, strFind, strNew)
EndFunction

Use SqlReplace instead of Replace in your query.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I am trying to use code to REPLACE for a string of text.
I want to use either SQL or VB. I do know that it looks
something like: Replace (string, search string, replace
string)

This is what I want to do: in Field12 of table Cats,
replace the word 'dogs' with blank.

In Field12

of table Cats

Replace (Like *dogs*, 'dogs', '')
Click to expand...


.
Click to expand...
 
Back
Top