Leading Zeros

[psbsmms]

I have a table that I want to use some standard building codes as the primary key. The standard codes have leading zeros oo the first 10 numbers that i want to maintain. I can keep them by changing the field type to text but I'm not sure that is a good idea, is there another way of maintaining the leading zeros?

 

[hneal98]

The only otherway to do that would be to create another field that is text that would store the leading characters/numbers.

Why would that not be a good idea, if the number has leading zeros, it was obviously meant to be string data?

 

[lespaul]

and you should only make numbers that are going to be used in calculations "real numbers" (please excuse the pun!!)

If you are going to add, subtract, multiple or divide them - they are real numbers and should be stored that way.

How often do you add up zip codes? Phone numbers? Social Security numbers? All these "number" fields should be stored as text fields.

HTH

Leslie

 

[hneal98]

great point!

 

[psbsmms]

I guess i go back to my initial database instructor who never wanted a text field as a primary Key. Thanks for the help, I am not going to calculate on the field so I changed it to a text field. Thanks again.

 

[SkipVought]

Yesssssssss, Lessssssssssss! ;-)

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[hneal98]

There are people who do not like to make strings key fields, but we have a database in SQL Server and all the key fields are char (Which is the same as string for Access.) It is just a little more dificult to maintain since you have to generate the id yourself and also have to have validation to make sure you don't duplicate; where Access would do that for you using an auto number. But then the auto number has it's issues as well.

So, when push comes to shove, it is better to make numeric data strings if you don't do any calculations on them, as stated above by lespaul.