Sort

[fenhow]

Hi, I have a table with a field called "Payment" this has one final payment called Residual all others are numbered 1-24 for an example. I am trying to figure out how I can force the table to sort 1-24 and then make Residual the last record?
Thanks.

 

[plog]

You've got bigger problems than the issue you stated. Text doesn't sort like numbers, so the 'Residual' value is the least of your worries. Your field is going to sort like this:

1
10
11
12
13
...
2
20
21
22
23
24
3
4


So, if you want your data to sort properly, you should change the Payment field to numeric. Then make the residual payment be 25. That way it sorts properly...

But I want it to say 'Residual'

Well, you can do that in a query by creating a calculated field based on Payment. It would look like this:

PaymentValue: Iif(Payment=25, "Residual", Payment)

 

[fenhow]

Great, thanks. So what if I had 36 payments not 24. Is there a way to calculate the last number in the DB with the IIF stmt?
Fen

 

[plog]

You could use a DMax (http://www.techonthenet.com/access/functions/domain/dmax.php) or a subquery.

 

[arnelgp]

use a query instead to sort your table:

select *, format([Payment], "00") As SortOrder From [yourtable] ORDER BY format([Payment], "00")