how to set the field with no zero in the beginning

[Hema Selvan]

I have question about how to save record in table without 0 in the beginnng. I have created databse in which Item number is a field. Item number have more than 15 numbers. I used text instead of numbers. When i scan item number some start with 0 in the beginning and some dont. there are chances to scan same numbers again. when I scan it is dupliating. when i scan first with 0 in the beginning and no 0 next time. for instance, item number is 456789. When i scan, it appears as 0456789 as it shows that way in label. but in the box it shows as 456789. if I scan from the box it takes as 456789. they both are same but just becasue there is 0 in the beginning, it is stored as two different item number. how do I avoid this?

[dsatino]

Ok, I think I understand your problem, but I'm not exactly sure. My interpretation is that you can scan the same exact item and get two different item numbers depending on whether you scan the label or box. If that's the case, you basically have two options:

1. if your item numbers will always be numeric, then simply change the item number field to a numeric and the leading zero's will be dropped automatically. Numeric indexes are faster anyway, so unless you have an absolute need to store them as text you just change it.

2. If you need to keep the text format for the item number field then you can test if the item number is numeric and then format it to fit your needs.

[Hema Selvan]

Thanks for your reply. The reason I have Item number is Text format is they are always more than 15 digits. so If I have in number, they are not accepting it. Do you have any other options please. Thanks

[gershwyn]

The default numeric type is long integer, which is not capable of storing a 15-digit number, but there are types that are.

Change the data type to Number, then below in the field properties change Field Size to decimal and precision to how many digits you need (up to 28, in Access 2007.)

[dsatino]

Size of the number only matters if the data type you use for your field is to small to accept it.

What's the most number of digits you have in any item number?

[Hema Selvan]

I have 21 digits of number in Item number.

[Hema Selvan]

Thanks for your reply. I did the same to the database. I have almost 6500 records now. I got another problem after I changed the tables to number. Now I couldn't find the records. I enter the item number in Control F to find but it's not searching anymore. Can you please help me with this?

[dsatino]

Then change the field type to Decimal. Set the precision to 21 (or higher) and set the scale to 0.

[Hema Selvan]

Thanks for the reply. I did this already and it is working. I do have another problem. Now I couldnt search the record. I enter or scan the item number using Control F to find. But it is not searching.. I tried with more than 20 numbers. No searching at all.. Can you please help me with this issue. Thanks.