Incorrect Ascending Sort on Currency formatted column in listbox

[SpeedThink]

Appreciate assistance concerning the following problem;

Background: Upon clicking on an option button to sort data in ascending order within listbox, List2, the data
is not sorted correctly. For example, the order of the KarianceCurrent column is
$1991.75, $1.00, $1.45, $1.68, $1.81, $10,000.05 which is incorrect!


Row Source for Listbox, List2

SELECT qryPopulateListbox.AcctOrig, qryPopulateListbox.AcctCurrent, qryPopulateListbox.AcctNo, qryPopulateListbox.MRN, qryPopulateListbox.KarianceCurrent, qryPopulateListbox.Balance, qryPopulateListbox.DischDate, qryPopulateListbox.ExpReimbCurrent, qryPopulateListbox.DateAssigned, qryPopulateListbox.StatusCode
FROM qryPopulateListbox
ORDER BY qryPopulateListbox.AcctNo;


qryPopulateListbox

SELECT tblHospVarRpt.AcctOrig, tblHospVarRpt.AcctCurrent, tblHospVarRpt.AcctNo, tblHospVarRpt.MRN, Format(tblHospVarRpt.KarianceCurrent,"Currency") AS KarianceCurrent, Format(tblHospVarRpt.BalanceCurrent,"Currency") AS Balance, tblHospVarRpt.DischDate, Format(tblHospVarRpt.ExpReimbCurrent,"Currency") AS ExpReimbCurrent, tblHospVarRpt.DateAssigned, tblHospVarRpt.StatusCode
FROM tblHospVarRpt
WHERE (((tblHospVarRpt.AssignedTo) In (select employee_id from Employee_Access_List)));

 

[wware]

It's possible I'm not fully understanding your issue but it appears to me that you are sorting by AcctNo, not by KarianceCurrent.

 

[RuralGuy]

FYI the output of the Format() function is *always* Text!

 

[SpeedThink]

Although I am formatting the column within the listbox as currency, it is really text?

Consequently, it appears that I should cancel the formatting as currency.

Additional thoughts/comments?

 

[SpeedThink]

Another thought,

In the event that I cannot format as currency and sort in ascending order, is it possible to right justify all numbers within a column in a listbox?

 

[RuralGuy]

It is ok to Format the column but to get the sort you want, sort on the Unformatted data.