0

This might be a simple question, but surprisingly I haven't found anything that fits here. I've made a query in which I format a number as currency. Specifically:

Format((Sum([CombineTables].[Total Extended Price])),'Currency')

Now when I try to sort on Access it does something like this:

$9,994,340.95
$9,200.50
$9.75
$8,000,0000.00
$4,000
$1,000,000,000.00

I would like to rank these through SQL, and sorting i think would be the easiest way to do this. Do you know how to correct this problems, the outputs still need to be in the currency format.

Thank you in advance

Dm3k1
  • 187
  • 3
  • 8
  • 21
  • 2
    looks to me like it's treating/sorting it as a string, you should add another 'column' for `Sum([CombineTables].[Total Extended Price])` and sort on that. in the query designer you can check/uncheck a box for displaying it in the output iirc – T I Mar 29 '13 at 21:30
  • Good workaround, thank you... I'd like to mark your comment as the answer for your credit, and to avoid unneccesary reads - But unable. Will try again in awhile. Thank you. – Dm3k1 Mar 29 '13 at 21:43

2 Answers2

1

Well, one option is to order by the original value:

order by Sum([CombineTables].[Total Extended Price])

If you can't do that for some reason and everything has the same format, use this trick:

order by len(TotalExtendedPrice), TotalExtendedPrice

(or whatever the column is).

You can handle decimals with this trick:

order by len(TotalExtendedPrice) - iif(TotalExtendedPrice like '*.*',  3, 0),
         TotalExtendedPrice
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That's a cool trick, but I don't think it will work in his case because from the sample date sometimes he includes decimals and sometimes not, so your sort would rank $1,000.00 as larger than $9,999 – Dan Metheus Mar 30 '13 at 17:50
1

looks to me like it's treating/sorting it as a string, you should add another 'column' for Sum([CombineTables].[Total Extended Price]) and sort on that. in the query designer you can check/uncheck a box for displaying it in the output iirc

T I
  • 9,785
  • 4
  • 29
  • 51