Can't change record field value in linked table

[JenniferCheng]

Hi,
I have a frontend access 2002 connecting to Oracle server through odbc. I need to update many record value in linked table T1. I want to replace field "Customer_id" beginning with "MD" to beginning with "FH", and there are around 5000 records. Then I need use "find and replace" function. But "replace" function is disabled. How can I solve this problem? If using script, I need write 5000 scripts. Is there any good method?
Thanks in advance.
Jennifer

 

[PHV]

Have you tried an Update query ?
UPDATE yourTable
SET Customer_id = 'FH' & Mid([Customer_id], 3)
WHERE Customer_id Like 'MD*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JenniferCheng]

Thanks. But it still doesn't work.

Here is script:
UPDATE Customer_Inforamtion
SET Customer_id = 'FH' & Mid([Customer_id], 4)
WHERE Customer_id Like 'MD*' ;

There are totally 6 digits of field Customer_id.

After run this script, get message "Enter value for mid:".
After enter 0190, get message:
old: 1: update Customer_information set Customer_id='FH'& Mid([Customer_id],4) where Customer_id like 'MD*'

new: 1: update Customer_information set Customer_id='FH'0190([Customer_id],4) where Customer_id like 'MD*'

ERROR at line 1:
ORA-00933: SQL command not properly ended

Do you have some link to use "set" and "Mid"? Thanks very much.

Jennifer