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
|
|||
|
|||
large number, store and sort
Two types of numbers.
First type: I have a 20 digit number that will be scanned into the database and it can store with or without spaces (I am presently planning to store it without spaces). The number will not be used for math purposes but will need to be sorted on the first seven of the last eight digits (see below) Example: 7780 1474 3470 0227 3664 Needs to be sorted on 0227 366 Sorting on these numbers will put the whole number in the right sequence (ascending or descending) for reports etc. What field type would be best to use - text, number etc.? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I set up to sort (first seven of last eight digits)? Second Type: C 108900 P 00516338 This number can be scanned and stored with or without spaces. I would like to store only digits greater than 0 from the 00516338, in this case 516338. (Let me know if this is a question for Forms, Forms programming since I think it will need to be set up in one of the events of the text box when the entry is made. What field type would be best to use? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I store only digits greater than 0 from the last eight digits? Thank you in advance for your help. Allan |
#2
|
|||
|
|||
Hi Allan,
For most purposes it's best to think of this sort of thing as a string of digits rather than a number: especially as the basic number types offered by VBA and Jet don't offer 20 digit precision! If you need to sort on just some of the digits it would seem that this isn't a single piece of data but actually consists of 3 or more fields (the first 12, the next 7, and the last 1 which is presumably a checksum). If so, there's a strong case for storing it in 3 or more fields. Index the 7-digit field and Bob's your uncle. (Otherwise, you have to sort on an expression, e.g. ORDER BY Mid([TheNumber], 13, 7) ). For the second kind of number, again it's worth storing it in four pieces (especially if you want to dump the leading zeroes in the last piece). Using a Number (Long) field here (where there aren't too many digits) means any leading zeroes are automatically dumped and the field still sorts correctly. On Thu, 19 Aug 2004 11:38:55 -0700, "AHopper" wrote: Two types of numbers. First type: I have a 20 digit number that will be scanned into the database and it can store with or without spaces (I am presently planning to store it without spaces). The number will not be used for math purposes but will need to be sorted on the first seven of the last eight digits (see below) Example: 7780 1474 3470 0227 3664 Needs to be sorted on 0227 366 Sorting on these numbers will put the whole number in the right sequence (ascending or descending) for reports etc. What field type would be best to use - text, number etc.? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I set up to sort (first seven of last eight digits)? Second Type: C 108900 P 00516338 This number can be scanned and stored with or without spaces. I would like to store only digits greater than 0 from the 00516338, in this case 516338. (Let me know if this is a question for Forms, Forms programming since I think it will need to be set up in one of the events of the text box when the entry is made. What field type would be best to use? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I store only digits greater than 0 from the last eight digits? Thank you in advance for your help. Allan -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
John,
I understand what you are saying and I think I know one way I can do this. Create however many fields in a table I want and make text boxes on the form for each field. Make one unbound text box and in the After Update make a declare a variable for the input number with put code that will populate each of the entry text boxes with the portion of the number I want in that table field. The one thing I am not certain about is index the 7-digit field. Does that mean make it so it will not accept duplicates? I am not clear on indexed and non indexed fields. Thanks Allan -----Original Message----- Hi Allan, For most purposes it's best to think of this sort of thing as a string of digits rather than a number: especially as the basic number types offered by VBA and Jet don't offer 20 digit precision! If you need to sort on just some of the digits it would seem that this isn't a single piece of data but actually consists of 3 or more fields (the first 12, the next 7, and the last 1 which is presumably a checksum). If so, there's a strong case for storing it in 3 or more fields. Index the 7-digit field and Bob's your uncle. (Otherwise, you have to sort on an expression, e.g. ORDER BY Mid([TheNumber], 13, 7) ). For the second kind of number, again it's worth storing it in four pieces (especially if you want to dump the leading zeroes in the last piece). Using a Number (Long) field here (where there aren't too many digits) means any leading zeroes are automatically dumped and the field still sorts correctly. On Thu, 19 Aug 2004 11:38:55 -0700, "AHopper" wrote: Two types of numbers. First type: I have a 20 digit number that will be scanned into the database and it can store with or without spaces (I am presently planning to store it without spaces). The number will not be used for math purposes but will need to be sorted on the first seven of the last eight digits (see below) Example: 7780 1474 3470 0227 3664 Needs to be sorted on 0227 366 Sorting on these numbers will put the whole number in the right sequence (ascending or descending) for reports etc. What field type would be best to use - text, number etc.? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I set up to sort (first seven of last eight digits)? Second Type: C 108900 P 00516338 This number can be scanned and stored with or without spaces. I would like to store only digits greater than 0 from the 00516338, in this case 516338. (Let me know if this is a question for Forms, Forms programming since I think it will need to be set up in one of the events of the text box when the entry is made. What field type would be best to use? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I store only digits greater than 0 from the last eight digits? Thank you in advance for your help. Allan -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. . |
#4
|
|||
|
|||
Allan,
You can set an index to either accept or reject duplicate values. You can also create indexes that include more than one field, so you could have one index for the entire 3 or 4-field "number" and another for just the 7 "sortable" digits. On Thu, 19 Aug 2004 15:35:22 -0700, "AHopper" wrote: John, I understand what you are saying and I think I know one way I can do this. Create however many fields in a table I want and make text boxes on the form for each field. Make one unbound text box and in the After Update make a declare a variable for the input number with put code that will populate each of the entry text boxes with the portion of the number I want in that table field. The one thing I am not certain about is index the 7-digit field. Does that mean make it so it will not accept duplicates? I am not clear on indexed and non indexed fields. Thanks Allan -----Original Message----- Hi Allan, For most purposes it's best to think of this sort of thing as a string of digits rather than a number: especially as the basic number types offered by VBA and Jet don't offer 20 digit precision! If you need to sort on just some of the digits it would seem that this isn't a single piece of data but actually consists of 3 or more fields (the first 12, the next 7, and the last 1 which is presumably a checksum). If so, there's a strong case for storing it in 3 or more fields. Index the 7-digit field and Bob's your uncle. (Otherwise, you have to sort on an expression, e.g. ORDER BY Mid([TheNumber], 13, 7) ). For the second kind of number, again it's worth storing it in four pieces (especially if you want to dump the leading zeroes in the last piece). Using a Number (Long) field here (where there aren't too many digits) means any leading zeroes are automatically dumped and the field still sorts correctly. On Thu, 19 Aug 2004 11:38:55 -0700, "AHopper" wrote: Two types of numbers. First type: I have a 20 digit number that will be scanned into the database and it can store with or without spaces (I am presently planning to store it without spaces). The number will not be used for math purposes but will need to be sorted on the first seven of the last eight digits (see below) Example: 7780 1474 3470 0227 3664 Needs to be sorted on 0227 366 Sorting on these numbers will put the whole number in the right sequence (ascending or descending) for reports etc. What field type would be best to use - text, number etc.? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I set up to sort (first seven of last eight digits)? Second Type: C 108900 P 00516338 This number can be scanned and stored with or without spaces. I would like to store only digits greater than 0 from the 00516338, in this case 516338. (Let me know if this is a question for Forms, Forms programming since I think it will need to be set up in one of the events of the text box when the entry is made. What field type would be best to use? If number is the right choice, what field size - Long Integer, Single, Double etc.? How would I store only digits greater than 0 from the last eight digits? Thank you in advance for your help. Allan -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. . -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
auto filter / sort | Bernie Deitrick | Worksheet Functions | 0 | June 1st, 2004 01:04 PM |