Where's the REPLACE function?

[roboninja]

I am used to using SQL Server, and the REPLACE function to replace characters in a string. What is an equivalent function in Access? I can't seem to find one.
Thanks.

 

[jitter]

Access 2000 does have the Replace function. If you are using Access 97 or lower you might try looking into the Mid function. Basically if you work with instr and mid you can get the same effect of replace but through a round a bout way.

Hope this helps....

 

[nickjar2]

If u r using Acc 2000 there is a Replace function. If u r using a version prior to 2000 then u may have to create your own function. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[roboninja]

Hmmm, I am using Access 2000, but when I put REPLACE in a SQL statement, it gives me an undefined procedure error. I might just end up doing it in SQL Server.

 

[roboninja]

The exact error is "Undefined function 'REPLACE' in expression." Here's the SQL statement:
UPDATE Ind_Construction_Costs SET PriceIndConstrCost1 = REPLACE(PriceIndConstrCost1, ".5-", ".50-&quot

 

[MichaelRed]

I do not know why Ms. A won't accept "REPLACE", but in general, SQL would accept an update using the Where (Criteria in Ms. A. Query Grid) and the New value in the Set (Update To in Ms. A. Query Grid). the syntax is ~~

UPDATE Ind_Construction_Costs SET Ind_Construction_Costs.PriceIndConstrCost1= ".50-"
WHERE (((Construction_Costs.PriceIndConstrCost1)=".5-&quot);

I believe this syntax is acceptable to most SQL dialects.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

The Replace function is available in VB but not in a query. (The JET parser doesn't include Replace.) You can create your own function and call it from a query.

Public Function MyReplace(sStrIn As String, sStr As String, sRep As String) As String
MyReplace = Replace(sStrIn, sStr, sRep)
End Function

UPDATE Ind_Construction_Costs
SET PriceIndConstrCost1 = MyReplace(PriceIndConstrCost1, ".5-", ".50-&quot Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.