update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Erik said:
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.
Click to expand...

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
 
Erik said:
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.
Click to expand...

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")
 
Back
Top