Leading zero's in number fields

[David]

Hello everyone,
I'm creating a new database in Access 2002 and I have a field (Invoice
number) that I would like to be a number field but also allow for leading
zeros (ex: 025337). I have tried leaving the format blank as well as
changing it to General Number and niether allows a leading zero. None of the
other format options seem to make sense for this. Any ideas? Do I need to
switch the data type from number to text? Thank you very much.

 

[Jeff Boyce]

David

Be aware that there's a difference between what is stored and how it gets
displayed.

You already received a response that suggested changing your "number" to
"text". If you will need to "do math" on your "number" (i.e., add,
subtract, multiply, divide), do NOT change it to text.

But if your "number" isn't really a number, but a "code", and if you don't
need to "do math", by all means, call it what it really is, text (which
happens to be digit characters).

If, on the other hand, you DO need to "do math" with it, keep it as a number
data type field, but use formatting to display it the way you wish. Check
Access HELP for the Format() function...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hello everyone,
I'm creating a new database in Access 2002 and I have a field (Invoice
number) that I would like to be a number field but also allow for leading
zeros (ex: 025337). I have tried leaving the format blank as well as
changing it to General Number and niether allows a leading zero. None of the
other format options seem to make sense for this. Any ideas? Do I need to
switch the data type from number to text? Thank you very much.

The numbers 1, and 001, and 0000001 are all THE SAME NUMBER.

Don't confuse data *presentation* with data *storage*. If you're going to be
using math on this Invoice Number - say, code to increment the highest
existing value to create a "custom autonumber" - then you can use a Number
(probably Long Integer) datatype, and set its Format property to

"000000"

to *display* (not store, what's stored is a binary bitstring) leading zeros.
If you're assigning the invoice number in some other way then by all means use
Text as the datatype.

 

[BruceM]

We don't know for sure that there is no need to perform math with the number
(incrementing it, for instance), so the recommendation to switch to text may
be premature. However, it can remain as a number, with no need for type
conversion. Apply the format 000000 to the text box Format property, or use
the Format function, particularly if there is a need to concatenate other
text. For instance, as a text box Control Source:
="Invoice #" & Format([InvoiceNumber],"000000")

It can also be applied in a query, or via VBA.

Concatenating some number of zeros at the beginning, or none at all,
depending on the length of the number, seems needlessly complex.

Hello David,

Have you ever seen a real number that began with zero? There is no such
thing! So Yes, you need to switch the data type to text. OR, you could
keep the numerical part of your data, use CStr to convert it to a string
and concatenate a text 0 to the beginning when you needed it.

Steve
(e-mail address removed)

 

[BruceM]

Something strange must have gone on with my newsreader sort order or
something. This post ended up at the top of my list, and there was only one
response to the OP, but now I see that it is a few days old and that there
have been several replies.

We don't know for sure that there is no need to perform math with the
number (incrementing it, for instance), so the recommendation to switch to
text may be premature. However, it can remain as a number, with no need
for type conversion. Apply the format 000000 to the text box Format
property, or use the Format function, particularly if there is a need to
concatenate other text. For instance, as a text box Control Source:
="Invoice #" & Format([InvoiceNumber],"000000")

It can also be applied in a query, or via VBA.

Concatenating some number of zeros at the beginning, or none at all,
depending on the length of the number, seems needlessly complex.