Find / Replace

[Graeme]

In a field, I would like to update all instances of "Daily Billing" to
"Rental Payment - Daily Billing". The Replace function works the first time,
but when I add data to the file, and run the query again, it updates the
fields which have already been upated to "Rental Payment - Rental Payment -
Daily Billing". I can see why it is doing this, but is there a way for the
update query to look at just the first 11 characters of the field and only
perform the update if it finds the "Daily Billing" text there?
Thanks.

 

[KARL DEWEY]

Use "Daily Billing" as criteria.

 

[John Dumay]

Graeme,

IIF(Left(MyString, 11)="Daily Billing","Rental Payment - Daily Billing",
MyString)

John

 

[John W. Vinson]

In a field, I would like to update all instances of "Daily Billing" to
"Rental Payment - Daily Billing". The Replace function works the first time,
but when I add data to the file, and run the query again, it updates the
fields which have already been upated to "Rental Payment - Rental Payment -
Daily Billing". I can see why it is doing this, but is there a way for the
update query to look at just the first 11 characters of the field and only
perform the update if it finds the "Daily Billing" text there?
Thanks.

You don't need the Replace function at all. Just use an Update query with a
criterion:

UPDATE yourtable
SET yourfieldname="Rental Payment - Daily Billing"
WHERE yourfieldname = "Daily Billing";

or, of course,

WHERE yourfieldname = "Rentl Payment - Rental Payment - Daily Billing"

to undo the damage done previously.

John W. Vinson [MVP]

 

[Terry Kreft]

John,
Unless of course he's run it 3 times in which case

.... WHERE yourfieldname = "Rental Payment - Rental Payment - Rental
Payment - Daily Billing"

<g>

Graeme,
Alternatively:-
One off query (to reset back to Dailiy Billing)
UPDATE yourtable
SET yourfieldname="Daily Billing"
WHERE yourfieldname LIKE "*Rental Payment - Daily Billing";

Followed by
UPDATE yourtable
SET yourfieldname="Rental Payment - Daily Billing"
WHERE yourfieldname = "Daily Billing";

As with any mass update like this, back up your mdb file first just in case
it all goes horribly wrong.