Updating text "numbers" w/ leading zeros

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

Guest

I searched for a prior answer to this question but couldn't find exactly what
I needed, so here goes. We have a database in which ID "numbers" (actually a
text field) are used to identify clients. It is the primary key. The newer
client IDs are now up into five "digits", but the older ones are only four.
The department head now wants ALL client numbers to be five "digits" long,
which means a leading zero will have to be added to all the existing four
"digit" IDs. Is there an easy way in which this can be done?

This ID number is the linking field in a one-to-many relationship to a table
in which we list all the services that client has received (date and units of
service) through a subform. Can the same update process be used to add a
leading zero to the four digit ID numbers in the service table? There are
several thousand service entries, and to do it manually would be a chore!
Thanks in advance for any help.
 
Susan said:
I searched for a prior answer to this question but couldn't find
exactly what I needed, so here goes. We have a database in which ID
"numbers" (actually a text field) are used to identify clients. It
is the primary key. The newer client IDs are now up into five
"digits", but the older ones are only four. The department head now
wants ALL client numbers to be five "digits" long, which means a
leading zero will have to be added to all the existing four "digit"
IDs. Is there an easy way in which this can be done?

This ID number is the linking field in a one-to-many relationship to
a table in which we list all the services that client has received
(date and units of service) through a subform. Can the same update
process be used to add a leading zero to the four digit ID numbers in
the service table? There are several thousand service entries, and
to do it manually would be a chore! Thanks in advance for any help.
Click to expand...

Use an Update query...

UPDATE TableName
SET [TableName]![ID] = Format(Val([TableName]![ID]), "00000")

(please test on a copy of your table)
 
First make sure the relations have Cascade Update Related Fields checked.

Use an update query like this SQL statement.
UPDATE NumberMain SET NumberMain.ID = Right("0000" & [ID],5);
 
Back
Top