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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|