Replacing text in a field..can't get solutions to work

[amkazen]

Hi, I have a table called "ts" with multiple fields. One of the fields is
titled titled "scrip". The "script" field is a memo data type as some of the
descriptions exceed 3,000 characters. The description data for approx. 1,400
records is currently generic, meaning it is the same description for all
1,400 records. I want to replace 2 words in the description data for each
record with a specific name. The two words are in the description twice: in
the first 25 characters and in the last 10 characters.

I have searched this newsgroup for similar problems and although there are
many instances, I can't seem to get the solutions to work for me.

The following SQL code does make the change I desire but the change is not
permanent. After running the query, I go and open my table and the data
still has the old 2 words. This is the case even after saving the query.

SELECT Replace([TS]![SCRIPT],"COTTON CANDY","CIRCUS CIRCUS(HAVE FUN), LAS
VEGAS, NV") AS SCRIPT
FROM TS
WHERE (((TS.PATH) Like "*FOOD\SUGAR*") AND ((TS.NAME) Like "*pur*") AND
((TS.TYPE)="omg"));

How can I make the change permanent? Thanks in advance.

Access 2003, XP
Andrew

 

[fredg]

Hi, I have a table called "ts" with multiple fields. One of the fields is
titled titled "scrip". The "script" field is a memo data type as some of the
descriptions exceed 3,000 characters. The description data for approx. 1,400
records is currently generic, meaning it is the same description for all
1,400 records. I want to replace 2 words in the description data for each
record with a specific name. The two words are in the description twice: in
the first 25 characters and in the last 10 characters.

I have searched this newsgroup for similar problems and although there are
many instances, I can't seem to get the solutions to work for me.

The following SQL code does make the change I desire but the change is not
permanent. After running the query, I go and open my table and the data
still has the old 2 words. This is the case even after saving the query.

SELECT Replace([TS]![SCRIPT],"COTTON CANDY","CIRCUS CIRCUS(HAVE FUN), LAS
VEGAS, NV") AS SCRIPT
FROM TS
WHERE (((TS.PATH) Like "*FOOD\SUGAR*") AND ((TS.NAME) Like "*pur*") AND
((TS.TYPE)="omg"));

How can I make the change permanent? Thanks in advance.

Access 2003, XP
Andrew

A Select query does not change stored data, you need an Update query.

Update TS Set [TS].[Script] = Replace([SCRIPT],"COTTON CANDY","CIRCUS
CIRCUS(HAVE FUN), LAS VEGAS, NV") WHERE TS.PATH Like "*FOOD\SUGAR*"
AND TS.NAME Like "*pur*" AND TS.TYPE ="omg";

In Access help, look up:
Update + Create an Update query

NOTE: "Name" and "Type" are reserved Access/VBA/Jet words and should
not be used as field names. Sooner or later it will cause a problem.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[amkazen]

Hi, Thank you for the help. Your answer worked.
Andrew

Hi, I have a table called "ts" with multiple fields. One of the fields is
titled titled "scrip". The "script" field is a memo data type as some of the
descriptions exceed 3,000 characters. The description data for approx. 1,400
records is currently generic, meaning it is the same description for all
1,400 records. I want to replace 2 words in the description data for each
record with a specific name. The two words are in the description twice: in
the first 25 characters and in the last 10 characters.

I have searched this newsgroup for similar problems and although there are
many instances, I can't seem to get the solutions to work for me.

The following SQL code does make the change I desire but the change is not
permanent. After running the query, I go and open my table and the data
still has the old 2 words. This is the case even after saving the query.

SELECT Replace([TS]![SCRIPT],"COTTON CANDY","CIRCUS CIRCUS(HAVE FUN), LAS
VEGAS, NV") AS SCRIPT
FROM TS
WHERE (((TS.PATH) Like "*FOOD\SUGAR*") AND ((TS.NAME) Like "*pur*") AND
((TS.TYPE)="omg"));

How can I make the change permanent? Thanks in advance.

Access 2003, XP
Andrew

A Select query does not change stored data, you need an Update query.

Update TS Set [TS].[Script] = Replace([SCRIPT],"COTTON CANDY","CIRCUS
CIRCUS(HAVE FUN), LAS VEGAS, NV") WHERE TS.PATH Like "*FOOD\SUGAR*"
AND TS.NAME Like "*pur*" AND TS.TYPE ="omg";

In Access help, look up:
Update + Create an Update query

NOTE: "Name" and "Type" are reserved Access/VBA/Jet words and should
not be used as field names. Sooner or later it will cause a problem.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'