Replace on PART of the Text in a COMMENT field ???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kev100 - 12 May 2008 23:23 GMT

I've got a field with several 100 characters. The exact composition of those
characters vary.

However, in all records, a certain exact phrase will always be present.

I'm needing to replace that phrase with another while keeping the rest of the
text the same.

Currently....I open the entire table...go to that field, and perform a
Find&Replace on that field using the "any part of field" match option. I
type the current phase into the Find What field and the new phrase into the
Replace With field.

This works fine and replaces all instances of that phrase.

I'd like to...if possible....use a parameter update query to do this so that
the replacement phrase can simply be entered into a pop-up box.

Is this possible...or the"manual" process currently being used the only
option?

Thanks very much.

Reply to this Message

fredg - 13 May 2008 00:19 GMT

> I've got a field with several 100 characters. The exact composition of those
> characters vary.
[quoted text clipped - 18 lines]
>
> Thanks very much.

Here is the SQL of an Update query, assuming you have Access 2000 or
newer.

Update YourTable Set YourTable.[FieldName] = Replace([FieldName],"Your
known phrase",[With what?])

Enter your own table and field names and the known phrase.

When you run the query you will be prompted to enter the replacement
text.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

kev100 - 14 May 2008 18:41 GMT

Thanks....that works great.

There actually 2 fields where this needs to be used.

It works very well on a field formatted as "Text" (field is in a linked sql
table)

However....another field is formatted as "varchar(256)." When the query runs
on this...it generates a formatting error (something about mismatched data
types...or something).

Both of these fields contain the same type of data (letters and text). I'm
not sure why they are different types in the sql table (I am not able to
change the formatting of those fields).

Should I be somehow specifying data types when using the query on a varchar
field (rather than a text field)?

Thanks very much....

>I've got a field with several 100 characters. The exact composition of those
>characters vary.
[quoted text clipped - 18 lines]
>
>Thanks very much.

Reply to this Message

kev100 - 14 May 2008 20:59 GMT

Had a chance to check that error message.....

It reads (in part):
---------------------
Title: Microsoft can't update all of the records in the update query.

Microsoft Access didn't udpate 597 field(s) due to a type conversion failure,.
..
...
To ignore the error(s) and run the query, click Yes.

----------------

(I've not selected Yes...always just cancel...to be safe).

Think forcing it through with the Yes would be okay?

Thanks...

Reply to this Message

fredg - 14 May 2008 23:08 GMT

> Had a chance to check that error message.....
>
[quoted text clipped - 14 lines]
>
> Thanks...

Please always include the relevant portion of any previous messages. I
certainly have no idea what you are talking about without going back
and searching my previous reply. It may only take a minute or so, but
it shouldn't be necessary. Notice my previous reply (and this one)
included your question. This will help others who read this message to
figure out what is happening and respond to you with an answer.

Probably would have been OK.
I suspect some of the records are Null in that field.

Add criteria to the query to avoid the Null records.

Update YourTable Set YourTable.[FieldName] = Replace([FieldName],"Your
known phrase",[With what?]) Where YourTable.[FieldName] is not null;

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

kev100 - 19 May 2008 18:41 GMT

Thanks very much.....

I did try just "Okaying" through the message...worked fine. The text was
correctly updated.

Thanks for the feedback...this will be a real time-saver.

>> Had a chance to check that error message.....
>>
[quoted text clipped - 16 lines]
>Update YourTable Set YourTable.[FieldName] = Replace([FieldName],"Your
>known phrase",[With what?]) Where YourTable.[FieldName] is not null;

Reply to this Message