Search code examples
sqlms-accessformatcurrencyranking

Access Wont Sort Currency by Total Value - SQL - Rank

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

Solution

  • 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