How to sort Currency

[mdnikki]

I am creating a report using this table below. One of the fields in that report is Giving Levels (GFlevel). See below. I want to sort them in ascending order but as you can see it isn't working since they all begin with "1". Any help is appreciated.

SumOfConstituent ID	SumOfEOM Pledge Amt	SumOfEOM Pledge Bal	CountOf2012 AAA Paid	SumOf2013 Cash or Pledge	GFlevel	SumOf2012 AAA Paid
279217430	$1,064,922.20	$833,896.18	430	$0.00	$1-$99	$17,081.50
81790109	$2,161,080.00	$1,578,610.39	132	$0.00	$1,000-$2,499	$175,518.00
524450878	$3,641,811.00	$2,690,808.69	834	$0.00	$100-$499	$157,756.25
6074636	$7,910,000.00	$5,106,490.76	10	$0.00	$10K+	$215,470.40
14572692	$902,618.00	$659,423.18	24	$0.00	$2,500-$4,999	$70,185.73
5362538	$683,450.00	$438,450.00	8	$0.00	$5,000-$9,999	$41,000.00
92943508	$1,251,660.00	$898,457.58	153	$0.00	$500-$999	$88,540.00

[JoeM]

Because it is a text field, it will not sort the way you want unless you change it so that all entries are formatted exactly the same, i.e.
$1-$99
becomes
$0,001-$0,099

That is rather ugly, and might not be the preferred method.

What you can do is create a new lookup/reference table, that has "GFLevel" (as you have shown it), and Lower Range and Upper Range in numeric format.
So your entry of $1-$99 would have 1 in Lower Range and 99 in Upper Range.

You could then link this reference table to your other table in a query, and use either of the Lower or Upper Range fields for sorting purposes. Then use this query as the Control Source of your Report.