Inserting zeros to the left of an existing record?

[RGALLEN]

I have a table I need to update in the following manner.

The records I am dealing with have a number in the table of which some are 5 digits & some are 6 digits. With the records that have 5 digits I need to add 3 zeros to the front of the number & the ones with 6 digits I need to add 2 zeros to the front of the number. I need to write the code in Access under the SQL view. Is there anyone out there that can help me. I have searched throughout many web site to try and find an example to follow with no luck.

[This message has been edited by RGALLEN (edited 02-26-2002).]

 

[SimonC]

I wasn't quite sure if you meant that you actually wanted to modify the data, or just change the table so the values appeared differently.

If you want to modify the data, you could use the format function:

Format([NumberField],"00000000")

So in an update query, that would be:

UPDATE <table name> SET <name of field> = Format(<name of field>,"00000000")

For this to work, the number field will actually have to be a text field, otherwise the leading zeroes will just get lost.

If you only want to change the display of the value, you could change the format property in the table to "00000000" (without the quotes), or use the format function above as part of a SELECT query.

Hope this helps.

Simon

 

[RGALLEN]

Thanks Simon, I will try it and see what happens. I appreciate your help.