update query

[Guest]

I have a table with records for example S-001,S-002,S-003 and i want to
update them to add an extra 0 in the front. I would like it to show
S-0001,S-0002,S-0003. Is there any way to do and update query to add the 0?
Thanks.

 

[Tom Lake]

I have a table with records for example S-001,S-002,S-003 and i want to
update them to add an extra 0 in the front. I would like it to show
S-0001,S-0002,S-0003. Is there any way to do and update query to add the 0?
Thanks.

Create an Update Query and under the field to be updated (I'll call it MyField) use
this
in the Replace With row:

Left([MyField], 2) & "0" & Mid([MyField], 3)

where Myfield is the name of your field.

Tom Lake

 

[Rick Brandt]

I have a table with records for example S-001,S-002,S-003 and i want
to update them to add an extra 0 in the front. I would like it to show
S-0001,S-0002,S-0003. Is there any way to do and update query to add
the 0? Thanks.

UPDATE TableName
SET [TableName]![FieldName] = Left([TableName]![FieldName], 2) & "0" &
Mid([TableName]![FieldName], 3)

The BETTER way to have set this table up is to store only the number in a
Long Integer field and then *display* it on forms and reports with the
expression...

=Format([FieldName], "S-000")

Then to make the change you simply change that expression to...

=Format([FieldName], "S-0000")