A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Numeric or Alpha



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 05:16 PM posted to microsoft.public.access.gettingstarted
Box666
external usenet poster
 
Posts: 60
Default Numeric or Alpha

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
  #2  
Old May 11th, 2010, 05:50 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Numeric or Alpha

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" wrote in message
...
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



  #3  
Old May 11th, 2010, 06:13 PM posted to microsoft.public.access.gettingstarted
Linq Adams via AccessMonster.com
external usenet poster
 
Posts: 1,474
Default Numeric or Alpha

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

  #4  
Old May 11th, 2010, 06:45 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Numeric or Alpha

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

  #5  
Old May 11th, 2010, 07:07 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Numeric or Alpha

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.