Using SUBSTITUTE function 3

[HenryAnthony]

Hi,

I need to change all instances of an apostrophe "'" in a text field to "\q". So I am using an update query with the "update to" field being:

SUBSTITUTE([apostext],"'","\q&quot

I get an error of "Undefined function". Am I wrong in thinking this function is available or do I have to implement it some other way?

Access 2000. Thanks.

 

[nicsin]

Hi,

use replace([apostext],"'","\q&quot instead.

Regards,
nicsin

 

[HenryAnthony]

Thanks nicin, but I still get "Undefined function 'replace' in expression". Any ideas?

 

[nicsin]

Oh do you mean you are using it inside the sql? I didn't realise that the first time. I don't think you can though. Are you developing an app? The way to proceed in my opinion is to write a utility which will get all the records in an ADO recordset, change them (using replace), and post them back.

regards,
nicsin

 

[TonyJollans]

Hi HenryAnthony,

SUBSTITUTE is an Excel worksheet function.
Replace is a VBA function, new in VBA 6 (Office 2K).

Neither is available directly in a Query. You can write and use your own functions, though, so in a code module create a function like ..

Function myReplace(Str1, Str2, Str3) as String
myReplace = Replace(Str1, Str2, Str3)
End Function

and use myReplace([apostext],"'","\q&quot in your Query.

Enjoy,
Tony

 

[CajunCenturion]

It depends on what version of Access, and more to point, what version of VBA that you're using. If you're using VBA version 6, then although you cannot use the Replace function directly in the SQL, you can write a wrapper function around the replace function. If you're using less than VBA version 6, you can still write the wrapper, but you'll have to code the Replace yourself.

In a module, create the following Public Function.

Public Function DoReplace (TheString as String, RepWhat as String, RepWith as String) as String
   DoReplace = Replace(TheString, RepWhat, RepWith)
End Function

and then in your SQL Statement

Set Fieldname = DoReplace(FieldName, "'", "\q")

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HenryAnthony]

Thanks folks - you all deserve stars! I used Cajun\qs code. It worked like a charm. I have no doubt the other suggestions would work as well and will stash them away in my code archive.

Also very valuable to me to learn non-Access help is in Access help - go figure.

Someone should write a FAQ something like "If you want to learn about Excel, go to Access help" ;o)

 

[nicsin]

Thank you HenryAnthony.

All the best!

nicsin

 

[ecosse]

Hi,

I'm trying to crack the same problem but cannot seem to copy either of the above functions into a module and then get the query to work. Each time I run the query I get a compile error in the module.....'Expected variable or Procedure not Module' error.

Do I need to have a specific reference checked ? If so which one ?

Many thanks

 

[HenryAnthony]

ecosse,

Can't speak to the errors you are getting. I just don't know Access THAT well. That being said, if I can get this to work, anybody should be able to. I think you need Access 2000 minimum for this to work.

I have the following in a standard module:

Public Function Repl(Expr As String, sFind As String, sRepl As String) As String
Repl = Replace(Expr, sFind, sRepl)
End Function

Then, in an update query (In my case, all instances of "\q" gets replaced with "'".):

Table: YourTable
Field: YourField
Update to: Repl([YourField],"\q","'")

Hope this helps,

Henry

 

[TonyJollans]

Hi ecosse,

The message Expected variable or procedure, not module means that you have a Module name where something else was expected. If you get the error using the function from here, I guess you have a [blue]Module[/blue] called [blue]Replace[/blue] - try renaming it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ecosse]

Cheers Chaps,

I'll have a look later on, been hijacked onto something else.

I'll keep you informed how i get on.



Mark