Terminal Digit Order

J

Janice

How can I sort (in a query) a column consisting of 9 numbers, in terminal digit order?
 
V

Van T. Dinh

Create a Calculated Field in your Query:

UnitsVal: [YourNumber] MOD 10

and then sort by this Calculated Field.

--
HTH
Van T. Dinh
MVP (Access)




Janice said:
How can I sort (in a query) a column consisting of 9 numbers, in terminal
Click to expand...
digit order?
 
J

Janice

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 00111341
000675150 00110112
001082840 00110963
001101120 00108284
001168350 00067515
000965360 00042605
001193410 00116835
001009630 00009536

Appreciate your time


----- Van T. Dinh wrote: ----

Create a Calculated Field in your Query

UnitsVal: [YourNumber] MOD 1

and then sort by this Calculated Field

--
HT
Van T. Din
MVP (Access




Janice said:
How can I sort (in a query) a column consisting of 9 numbers, in termina
Click to expand...
digit order
 
V

Van T. Dinh

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 said:
This works, thanks!
I need to ask a little more however. This sorts the numbers by last number
Click to expand...
only. How can it be altered to sort a string of numbers in progressive TD
order by twos? For example:
 
J

Janice

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 number

00113253
00117283
00172443
00028273
00047333
00172543

would be sorted as:
00113253
00028273
00117283
00047333
00172443
00172543

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 3
000 47 33 3
000 28 27 3
001 72 44 3
001 17 28 3
001 72 54 3
Then are sorted like this
Last 3rd 2nd 1s
001 13 25 3
000 28 27 3
001 17 28 3
000 47 33 3
001 72 44 3
001 72 54 3

Is there a way to do this in Access with the numbers all in one field
 
V

Van T. Dinh

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 said:
Not exactly. I would like to sort by the last to digits first, the second
Click to expand...
two digits next and so on, with out having to that the numbers
 
Top