Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Updating text "numbers" w/ leading zeros

Discussion in 'Information Technology' started by Susan, Nov 16, 2005.

  1. Susan

    Susan
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    Malaysia Hindus, Sikhs In Malaysia Appalled With University Textbook Ridiculing Their Faiths Breaking News Jun 14, 2016
    Hard Talk Taking One Liners Out Of Context From Gurbani To Allow Intellectual Independence? Hard Talk Apr 23, 2016
    India Protests in India Over the Desecration of a Holy Text Leave Two Dead Breaking News Oct 16, 2015
    Learn Punjabi Convert Punjabi text Language, Arts & Culture Oct 11, 2013
    Sikh Coalition Harmeet Kaur Took the Lead ! First to Respond to Textbook Challenge ! Sikh Organisations May 25, 2013

  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Susan wrote:
    > 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.


    Use an Update query...

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

    (please test on a copy of your table)

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    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);

    "Susan" wrote:

    > 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.
     
  5. Susan

    Susan
    Expand Collapse
    Guest

    Thank you, Rick & Karl...that's just what I needed!

    "Susan" wrote:

    > 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.
     

Share This Page