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
|
|||
|
|||
Terminal Digit Order
Do you mean you want to sort by the last digit first and if the last digit
of the numbers is the same then sort by the second last digit. If that is the case, keep the UnitsVal and create a second Calculated Value: TensVal: ([YourNumber] MOD 100) \ 10 This gives you the value of the second last digit. In your Query, you can then sort by UnitsVal and then TensVal. -- HTH Van T. Dinh MVP (Access) "Janice" wrote in message ... This works, thanks! I need to ask a little more however. This sorts the numbers by last number only. How can it be altered to sort a string of numbers in progressive TD order by twos? For example: As they sort now: Would like TD order by twos: 000426050 001113410 000675150 001101120 001082840 001109630 001101120 001082840 001168350 000675150 000965360 000426050 001193410 001168350 001009630 000095360 Appreciate your time. |
#2
|
|||
|
|||
Terminal Digit Order
Not exactly. I would like to sort by the last to digits first, the second two digits next and so on, with out having to that the numbers
001132539 001172839 001724439 000282739 000473339 001725439 would be sorted as: 001132539 000282739 001172839 000473339 001724439 001725439 Here is how I do it in Excel, however would like to be able to do it in one column in Access: Numbers begin like this (divided into four columns in Excel): 001 13 25 39 000 47 33 39 000 28 27 39 001 72 44 39 001 17 28 39 001 72 54 39 Then are sorted like this: Last 3rd 2nd 1st 001 13 25 39 000 28 27 39 001 17 28 39 000 47 33 39 001 72 44 39 001 72 54 39 Is there a way to do this in Access with the numbers all in one field? |
#3
|
|||
|
|||
Terminal Digit Order
Create 4 Calculated Columns:
1stSort: [YourNumber] MOD 100 2ndSort: [YourNumber] MOD 10000 3rdSort: [YourNumber] MOD 1000000 4thSort: [YourNumber] \ 1000000 and sort your Query according to the above 4 Columns in order. BTW, I suggest you check the spelling before you post which make it easy for potential respondents to understand. I had a bit of problem understanding your post until I realised you had "to" and "two" for number 2. -- HTH Van T. Dinh MVP (Access) "Janice" wrote in message ... Not exactly. I would like to sort by the last to digits first, the second two digits next and so on, with out having to that the numbers 001132539 001172839 001724439 000282739 000473339 001725439 would be sorted as: 001132539 000282739 001172839 000473339 001724439 001725439 Here is how I do it in Excel, however would like to be able to do it in one column in Access: Numbers begin like this (divided into four columns in Excel): 001 13 25 39 000 47 33 39 000 28 27 39 001 72 44 39 001 17 28 39 001 72 54 39 Then are sorted like this: Last 3rd 2nd 1st 001 13 25 39 000 28 27 39 001 17 28 39 000 47 33 39 001 72 44 39 001 72 54 39 Is there a way to do this in Access with the numbers all in one field? |
#4
|
|||
|
|||
Terminal Digit Order
Thank you so much. This works like a charm. I made four expressions as you suggested and sorted in ascending order (did not work until that was added). Appreciate your time and patience.
Also thanks for the caution on spelling....will be more careful in the future. |
Thread Tools | |
Display Modes | |
|
|