replace in a query

[LG]

I have a field with the following message:
Must be submitted prior to March 31 of the calendar year following the year
in which the service or supplies were received.
How do I query that and replace it with a number of 78? There are many
others that corralate to other numbers. I started to do this manually and I
know there is an easier way.
Thanks

 

[Phil Smith]

Don't understand what you mean here exactly. Do you want to replace
every record which has a field called "message", where the contents of
message "Must be submitted prior to March 31 of the calendar year
following the year in which the service or supplies were received." is
replaced with "78"?

If so, just build an update query, using "Must be submitted prior to
March 31 of the calendar year following the year in which the service or
supplies were received." as your criteria, and "78" as your update.

Note this will not put the number 78 but the text string "78" into that
field. If you need it to be a number, then once you are done and all of
those strings have been changed to numbers, you can change the design of
the table so that this field is a number, and the system will update
your table.

 

[Monish]

LG -

I might suggest setting up another table with the messages and their
corresponding "value" and linking the two tables by the message field in your
query to pull in the value...of course this depends upon the messages being
exactly the same in each instance, which could be the case if they are
selecting them from a drop-down list box on your form...

Otherwise you may have some cleanup to do before creating your 2nd table -
plus you would want to make sure that going forward all message selections
are standardized.

Hope this gives you somewhere to start.

MR

 

[Clifford Bass]

Hi,

A backup of the database would be a wise thing to do before attempting
the change.

I would maybe suggest a slight variation on Phil's solution, in that
you could create a new numeric field and set that to the numeric value
instead of doing the replace. Then if you mess up, you have only messed up
the new field, not the current field. Once you have successfully converted
everything correctly, then you can safely delete the old message field.

Clifford Bass

 

[Phil Smith]

I'll bet you have health insurance, car insurance, back up your computer
on a regular basis, and look both ways before you cross the street!

Good advice.

 

[Clifford Bass]

Hi Phil,

Ya sure! Experience the hard way has perhaps been a learning factor.

Clifford Bass