Access field number formats

[snowmantle]

Hi

I have got a field in a table that is used to hold a code for a product.

The code can be between 2-10 digits and sometimes the input has leading zeros sometimes it has trailing zeros.

I believe that this should be stored as a 10 digit number where leading zeros are added for any that are shorter than this.

Is it possible to format and validate this input as a number for being able to index it later?

Should this be text instead?

Which ever option is best could someone please give me an example of how to do it? and also how I would go about setting the format of the field in Access?

Examples

1155
591
020
1150
008
004
011
138
5051057654
401288005
800570991
404680210
505105765
800177000
317805211
317805212
317805311
400040090
400049280
400049285
400049290
400112000
400303510
400303580
400603400
403080030
403080031
403080032
403080033
403080034
403240033
403240044
403240055
403607200
403607260
403870022
403870023
403870030
403870034
403870040
403870042
403870043
403870044
403870057
403870082
403870098
403870099
404498302
404540708
410154000
590030002
590030020
590030023
761110007
800087089
800097000
800837580
803014113
803279334
803291961
841380010
871314500
871620060
872210000
872270080
900027570
900141101
900520010
801089305
401758741

 

[Lilliabeth]

In the format property for the number field: 0000000000

--Lilliabeth

 

[SkipVought]

Hi,

You will never do arithmetic on Product Code. It is an IDENTIFIER, not a NUMBER, although it may consist of Numeric CHARACTERS. I almost always store as TEXT, with a required length (nbr of characters).

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[snowmantle]

Thanks both. Is it enough to set the format to 0000000000 and the TEXT size to 10 or do I need to create a validation rule as well?

 

[snowmantle]

Also if so, what would the validation rule be? just 0000000000

?

 

[Lilliabeth]

Point taken Skip, and I know that we always hear that if the data would never be used for math, then it's text, but in all honesty, I never hear a good reason for that so I am left confused. Here is how I see it, and I am sure up for learning why I am mistaken. (I admit right up front my confusion!)

I think a numeric datatype offers a performance benefit... for example, a non-nullable field with the DOUBLE datatype takes a fixed 8 bytes, where UTF-16 TEXT is going to consume (for a 10-character string) 20 bytes + end of string pointer length (not sure of length)... if the rows work into the millions or billions 12+ bytes per record will sure be significant. It takes more time to load 4 million * 20 bytes than it does 4 million * 8 bytes...

Index size also substancially increases, right?

Correct me if I am wrong.

And of course, text sorts funny, which aside from being annoying, makes range searches slow.

So, what am I missing? Other than someday you might want to use alpha characters, why should we always automatically use text for part numbers?

--Lilliabeth

 

[SkipVought]

The validation rule is 10 numeric characters for text.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}