Replace and Update

[dig314]

MSAccess linking to Oracle 8 tables with Microsoft ODBC for Oracle
driver

I need to change the email address of all users because our domain has
changed. Is there a way to do this with SQL and not an Edit Replace
from Access?

SELECT REPLACE ('domainOld.com ', 'domainOld.com' ,
'domainNew.com' ) FROM User_Table

This query returns the correct information. How could I write an UDPATE
query to do the same thing ?

Thanks for your time.

Dig

[Ian Hinson]

Well, you could use Replace directly in an SQL update query
ie. UPDATE User_Table
SET EmailAddr = Replace([EmailAddr], "olddomain.com" , "newdomain.com" );

But.. it would fail if any user has a Null email address, since Replace
cannot accept a Null parameter.

Safer to write a user-defined function eg.

Public Function ReplaceDomain(B yVal emailaddr as Variant) as Variant
If IsNull(emailadd r) Then
ReplaceDomain = Null
Else
ReplaceDomain = Replace(emailad dr, "olddomain.com" , "newdomain.com" )
End If
End Function

then use ReplaceDomain in the UPDATE query as shown above.

HTH,
Ian.
<di****@yahoo.c om> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

MSAccess linking to Oracle 8 tables with Microsoft ODBC for Oracle
driver

I need to change the email address of all users because our domain has
changed. Is there a way to do this with SQL and not an Edit Replace
from Access?

SELECT REPLACE ('domainOld.com ', 'domainOld.com' ,
'domainNew.com' ) FROM User_Table

This query returns the correct information. How could I write an UDPATE
query to do the same thing ?

Thanks for your time.

Dig