Add Zeros to table

[Kelly]

I was given a database with hundreds of thousands of files. I have to update
it and in order to do so I have to first simplfy. Here is what I have.

I have a "Box" column in a table. In the table I have numbers and letters
Example
v0825
1
125
2

Can I add zeros to the table, and will it work if some fields have text.
This is what I want
v0825
00001
00125
00002

Also, I want to do this in the table because this is what they are using for
their searches. I will eventually clean this mess up, but need to do this
task first.

 

[Jeff Boyce]

Kelly

From your description, you are trying to "zero-pad" some values in a field,
rather than "the table".

Since "00001" and "1" are the same number, you have a couple choices.

First, if what is stored MUST be "00001", you'll need to make sure the field
is of type "text" before updating the values.

Or, if what must be DISPLAYED is "00001", you could consider storing numeric
values and formatting what gets displayed. HOWEVER, if you have
alphanumeric values (e.g., "v0825"), it's TEXT!

Before running an update query, make sure you have a backup copy of the
database/file. Update queries modify things.

Good luck


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Klatuu]

It does work.

Try this one:

UPDATE <Table> SET <Field> = Format(<Field>,"00000");

but i don't believe this works with data with letters

: Make a back up before doing this, just in case

 

[John W. Vinson]

I was given a database with hundreds of thousands of files. I have to update
it and in order to do so I have to first simplfy. Here is what I have.

I have a "Box" column in a table. In the table I have numbers and letters
Example
v0825
1
125
2

Can I add zeros to the table, and will it work if some fields have text.
This is what I want
v0825
00001
00125
00002

Also, I want to do this in the table because this is what they are using for
their searches. I will eventually clean this mess up, but need to do this
task first.

Just to add to the other suggestions... yes, the field must be of Text
datatype. If you want every record to have this field containing five
characters, left zero filled, you can run an update query updating it to

Right("00000" & [Box], 5)

Do this with caution - if there are Box values bigger than six characters you
will lose data!!! Do a query first searching for these:

SELECT * FROM yourtable WHERE Len([Box]) > 5;

to be sure you're not destroying data.