use update query to add prefix to a field

[djlabreche]

Here is what I am attempting: I have a field that needs to be updated with a prefix. Such as "1501" needs to be "UK1501". This needs to occur in this field in every record in the table. This process will need to repeated each time the database is used for a different project...about 20 times a year.

[pbaldy]

Personally I wouldn't bother to update the field. I'd have the prefix separately somewhere, either in a one-record table or on a form, then concatenate the prefix with the value on the fly. If you want to update the field, you can use an update query, but you'd have to strip off the previous prefix as well, would you not?

[djlabreche]

Actually, I failed to include some key information. The prefix can be any one or two alpha characters. It needs to be entered by the user. The database will be used by each project independently. That is, it will be copied to the new project and the copy be used by that project exclusively.

However, I will need to give the user the option of changing the prefix so yes I will need to strip it as you mention.

[pbaldy]

All the more reason not to save the values together in my mind. I'd have a form for the user to enter the prefix and just concatenate that with the field in your forms/reports. If you really want to save it, use a form for the user to enter the old and new prefixes and use an update query, using the Replace() function to get rid of the old inside a concatenation with the new:

NewPrefix & Replace(FieldName, OldPrefix, "")