Numeric or Alpha

[Box666]

I am just starting to build a table, i have a RefNo which must always
be 10 numbers long, so I have set the field as numeric.

Some of these RefNo's begin with a "0" or even "00", after i have put
them in the first 0's dissapear. The field is set as numer does this
mean i will have to set the field as text so that the first 0's appear
and hold in place.? or is there something i can do to force the 0 to
stay in place and still keep the field numeric.

I also need to ensure that there are always 10 digits in the RefNo
field, with any missing numbers being shown as 0 at the start of the
number.(sorry i may have asked the same question twice.)

The RefNo field will become very important latter as i had wanted to
use it as the primary key, and it will be used in a lot of search
querys

 

[Douglas J. Steele]

The only way to keep leading zeroes is to make the field text.

Unless you're planning on doing arithmetic using the field, there's really
no reason to make it numeric. In fact, since Long Integers cannot exceed
2147483647, you probably don't want a numeric field!

Note that this will increase the size of your database slightly (a ten
character text field is 10 bytes, whereas a long integer is only 4 bytes),
but it's doubtful that will have a significant impact on your application.

 

[John Spencer]

Use a Text field with the following Validation Rule
Validation rule: Like "##########"

One reason to use a text field is that the size of a Long integer will not
allow you to have a number larger than approx 2.15 billion. You could use a
double type to get around that limit.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County