leading zeros

[Robb @ FLW]

I have a table with a list bldg numbers some are 3 characters and others are
4 characters, the question is how can I place the leading zeros before each
bldg number for example I have a building 100 it needs to be 00100 or 01200
how can I make access do this for me?

Thanks for your support

 

[RBear3]

In the table, you can do so by changing the field to text. The number 0100
is simply stated as 100. If the leading zeros are important, then it is not
a "number" as far as Access is concerned. Numeric fields are used primarily
if you will be doing math on the numbers. Will you every have a 0100A? if
so, it definitely needs to be a text field.

That being said, what it looks like in your tables is really not important.
Your users should not be in the tables. They should be in your forms,
reports, and maybe queries. To handle this in your forms and reports,
simply use the FORMAT option.

 

[Robb @ FLW]

I forgot to mention that the field is already a text field, the reason behind
the leading 0 or 00's is that this will be imported into a differnt data base
I am not for sure if it is SQL or not. What is the format command that I am
looking for?

 

[RBear3]

Sorry, you are now trying to go back and add a zero?

I guess you would build a query to pull all entries with a LEN of 3. Then
you would update the field to be "0" & the existing field entry.

 

[Robb @ FLW]

I was affraid of that,,, we are looking at over 4000 bldg entries to do this
to.

 

[fredg]

I forgot to mention that the field is already a text field, the reason behind
the leading 0 or 00's is that this will be imported into a differnt data base
I am not for sure if it is SQL or not. What is the format command that I am
looking for?

You wish all the records to contain 5 digits in this field?
Run an Update query on the text field.

Update YourTable Set YourTable.FieldName =
Format([FieldName],"00000");

 

[Robb @ FLW]

Will give that a try
--
Robb

I forgot to mention that the field is already a text field, the reason behind
the leading 0 or 00's is that this will be imported into a differnt data base
I am not for sure if it is SQL or not. What is the format command that I am
looking for?

You wish all the records to contain 5 digits in this field?
Run an Update query on the text field.

Update YourTable Set YourTable.FieldName =
Format([FieldName],"00000");