Update Table - One field - two expressions?

[Guest]

Hi All,

I need to update multiple fields in my table and one field needs to be
updated twice, that is, I need to remove the first character (an ') and also
insert an hyphen. The updates run fine separately, but I don't know how to
update the same field twice in one query. Do I need to run a subquery?

Thanks,
BS

 

[John Spencer]

You should be able to do that all in one query

UPDATE YourTable
SET YourField = Mid(Left([YourField],4) & "-" & Mid([YourField],5),2)

That inserts a hyphen between the 4th and 5th characters in the string and
then trims off the first character from the resulting string.
It does assume that you have data in every record for the field (or that you
allow zero-length strings in this field).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[fredg]

Hi All,

I need to update multiple fields in my table and one field needs to be
updated twice, that is, I need to remove the first character (an ') and also
insert an hyphen. The updates run fine separately, but I don't know how to
update the same field twice in one query. Do I need to run a subquery?

Thanks,
BS

Do you man you simply wish to replace the first character which is
always a (') with a (-) ?

Update YourTable Set YourTable..FieldName =
Replace([FieldName],"'","-",1,1)

Or was this in addition to some other updating of the field?