Prev: Multi Search
Next: Output field as a text
From: Box666 on 11 May 2010 12:16
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
From: Douglas J. Steele on 11 May 2010 12:50
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.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Box666" <bob(a)mystical.demon.co.uk> wrote in message
news:20ab5d65-38c4-4a56-8e54-c7e594f1e169(a)b18g2000yqb.googlegroups.com...
>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


From: Linq Adams via AccessMonster.com on 11 May 2010 13:13
As Doug has said, fields containing all digits that are not being used for
mathematical operations, such as telephone numbers, SSNs and ID/Ref Numbers,
should be defined as Text.

You mention you are in the table creating stage, but presumably you will be
creating a form based on this table. Data entry should always be done thru a
form, rather than directly into the table.

In the form you can use the AfterUpdate event of the textbox that is bound to
the RefNum field to make sure that your data is always 10 chracters long,
with leading zeros padding out whatever is entered. Assuming that the textbox
on the form is named txtRefNum:

Private Sub txtRefNum_AfterUpdate()
Me.txtRefNum = Format(Me.txtRefNum, "0000000000")
End Sub

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

From: KenSheridan via AccessMonster.com on 11 May 2010 13:45
You could format the number to show 10 digits, with leading zeros, but I'd
agree with the others that a text data type is almost certainly more
appropriate here. A number is really a value with ordinal or cardinal
significance, or a measure of quantity, whether arithmetic operations on it
are required or not. In your case the fact that you require leading zeros
and the very scale of the 'number' pretty well rules that out.

Values made up of numeric digits are often encoding systems rather than
numbers; phone numbers, credit card numbers, ISBN numbers and ZIP codes are
examples. For these a text data type is appropriate as they have no ordinal,
cardinal or quantitative significance.

Ken Sheridan
Stafford, England

Box666 wrote:
>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

--
Message posted via http://www.accessmonster.com

From: John Spencer on 11 May 2010 14:07
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

Box666 wrote:
> 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
 | 
Pages: 1
Prev: Multi Search
Next: Output field as a text