Lead Zero Deletes?

[needadvice]

I have a numeric field for SSN with no dashes. When the user enters an SSN beginning with Zero it automatically deletes the zero. How do i get it to stay?
Please Help!

 

[Molby]

Put 00000 etc in Format in the Field's properties in the table design view

 

[lespaul]

while Molby's idea will solve your symptom, it doesn't cure your problem. You should always choose the proper datatype for the item. Numeric fields should be used when you are planning on calculating with the "number", how often do you multiply SSN's together? (People also set ZipCodes to Numeric fields, I don't usually perform any calculations with Zipcodes so I set them to text fields as well!) So, the proper answer to cure the problem is change the SSN field to TEXT. That will keep the leading 0's as well.

Leslie

 

[needadvice]

Molby,
Thanks for the response. It seemed to work at first but when the user went to query the record, the zero was gone again. Any ideas on how to make it stay?

 

[lespaul]

If you want the information in the table to have the leading zeros you will need to change it to a text field like I said before.


Leslie

 

[needadvice]

The problem with that is the user does not want it to be a text field. They don't want entry people to have the option of entering alpha. In addition, they have the same situation in another database and the zeros hold.
There must be a way.

 

[lespaul]

You can restrict the text field to only hold numeric information.

In addition, they have the same situation in another database and the zeros hold.

Then why don't you look at the properties of that field to see how they did it? It has to be either a text field that is restricted to numbers or a numeric field with some kind of input mask.


Leslie

 

[Craig0201]

SSN is not a number per se. You will not be performing any sort of calculation with it (a sum of SSNs????). It is therefore much better stored as a string.

This is pretty much a good rule of thumb. If you don't calculate with it, it's a string.

Craig

 

[lespaul]

That's what I've been saying Craig! Thanks for the support!

Leslie

 

[maxhugen]

I agree too, text is text.

May I suggest that you use a bit of code in the BeforeUpdate event to check for any characters that aren't numerical?

You can then cancel the update and advise the user that only numerical characters are permitted.

Max Hugen

http://www.AlphaFinancial.com.au

Sydney Australia

 

[mordeth]

thanks lespaul, I cant believe how easy this problem was and it had been pestering me for ages!!
But if needadvice doesnt want them to be able to enter letters then why doesnt he just use the input mask symbols that will only let you enter numbers? as in 0 or 9???