Access wont sort Correctly!

[Bob]

Access wont sort my query descending correctly?
It will not recognize credits in Currency eg:
$100
-$200
$300
Thanks for any help..Is there another way to sort my totals in a
Query?...............Bob







..........Jenny Vance

 

[Dirk Goldgar]

Access wont sort my query descending correctly?
It will not recognize credits in Currency eg:
$100
-$200
$300
Thanks for any help..Is there another way to sort my totals in a
Query?...............Bob



.........Jenny Vance

So is this Bob or Jenny?

I'm not aware of Access having any problem sorting Currency fields. Are
you sure the actual field type is Currency, and that you aren't being
confused by the Currency *format*? In the table design view, what does
it say is the Data Type of the field?

 

[Guest]

You did not say what was wrong with the sort. This is how it should sort if
you are using a datatype Number - Decimal - Currency.
Money
($200.00)
$100.00
$300.00

 

[Dirk Goldgar]

You did not say what was wrong with the sort. This is how it should
sort if you are using a datatype Number - Decimal - Currency.
Money
($200.00)
$100.00
$300.00

There is a bug in the sorting of Decimal fields; see this KB article:

http://support.microsoft.com/?id=837148
BUG: You may notice an incorrect sorted order
when you sort the negative decimal values in
descending order in Access

But currency values ought to be stored in the Currency data type if
possible, not Decimal.

 

[Bob]

There is a bug in the sorting of Decimal fields; see this KB article:

http://support.microsoft.com/?id=837148
BUG: You may notice an incorrect sorted order
when you sort the negative decimal values in
descending order in Access

But currency values ought to be stored in the Currency data type if
possible, not Decimal.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk it seems to be a function in Query that gives me my Currency
Payable:
Format(Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0),"Currency")
The amount left is what is owed which is what I cant seem to get sorted
Thanks...........Bob

 

[Bob]

Dirk it seems to be a function in Query that gives me my Currency
Payable:
Format(Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0),"Currency")
The amount left is what is owed which is what I cant seem to get sorted
Thanks...........Bob

Oh maybe it is decimal because in my db if I have one account that is split
1/3 1/3 1/3 , and say it was $100 and one client pays $33.33 It seems to
have a carry over of 1c even though he has paid total, so then I have to
enter his payment as $33.339 to clear the cent owing.............Thanks Bob

 

[Dirk Goldgar]

Dirk it seems to be a function in Query that gives me my Currency
Payable:
Format(Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidT
otal,0),"Currency")
The amount left is what is owed which is what I cant seem to get
sorted Thanks...........Bob

If you're sorting by that calculated field, you're sorting a text field,
not a number field at all. The Format() function returns a text string.
I suggest that you do one of two things:

EITHER

(a) remove the Format() wrapping from the calculated field, so the
expression is just this:

Nz(qTotalDueForPayment.TotalDue,0) -
Nz(qPaidAmountForPayment.PaidTotal,0)

and then use the Format *property* of the text box on your form or
report to apply the Currency format,

OR

(b) leave this calculated field as it is, but don't sort on it.
Instead, add the unformatted version of the field to the query -- with
the "Show" box unchecked -- and sort on that field.

I think option (a) would be the better choice.

 

[Bob]

Dirk , I looked at the Table and for [Field Name]PercentAmount, was currency
but the decimal placing was [Auto] is that OK?
Thanks Bob

 

[Bob]

If you're sorting by that calculated field, you're sorting a text field,
not a number field at all. The Format() function returns a text string.
I suggest that you do one of two things:

EITHER

(a) remove the Format() wrapping from the calculated field, so the
expression is just this:

Nz(qTotalDueForPayment.TotalDue,0) -
Nz(qPaidAmountForPayment.PaidTotal,0)

and then use the Format *property* of the text box on your form or
report to apply the Currency format,

OR

(b) leave this calculated field as it is, but don't sort on it.
Instead, add the unformatted version of the field to the query -- with
the "Show" box unchecked -- and sort on that field.

I think option (a) would be the better choice.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Now that I have taken that format away some of the totals about 80% of them
have 4 decimal points in their totals eg: this is an account fully paid but
is showing Due =$3,000.00
Paid =$3,000.00
Payable=0.0025
Is it possible for me to get my old totals back to 2 decimal placing and do
I leave this query without the $ format in Payable
Thanks for your help Bob

 

[Bob]

Now that I have taken that format away some of the totals about 80% of
them have 4 decimal points in their totals eg: this is an account fully
paid but is showing Due =$3,000.00
Paid =$3,000.00
Payable=0.0025
Is it possible for me to get my old totals back to 2 decimal placing and
do I leave this query without the $ format in Payable
Thanks for your help Bob

Figured out the format $#,##0.00;($#,##0.00)
Do you think its possible to get my balances back to only having last 2
digits with a double 00
Thanks bob

 

[Bob]

Never Mind seems to be sorting correctly now Thanks Bob

 

[Jamie Collins]

There is a bug in the sorting of Decimal fields; see this KB article:

http://support.microsoft.com/?id=837148

The DECIMAL bug (FWIW fixed for Access2007) only exhibits itself when
the order is descending and even then doesn't intermingle negative and
positive values. In fact, the only thing it gets wrong is to sort the
set of negative values in ascending order; the set of negative values
always correctly appears above the set of zeros, which appears above
the set of positive values (in correct descending order), which appears
above the set of nulls.

Jamie.

--

 

[Jamie Collins]

The DECIMAL bug (FWIW fixed for Access2007) only exhibits itself when
the order is descending and even then doesn't intermingle negative and
positive values.

I got the above bit right but must correct myself on the rest.

The sets of positive and negative values are indeed sorted correctly in
descending order; the bug is the order of appearance of the sets.

The correct order (top to bottom) is:

positives
zeros
negatives
nulls

The DECIMAL bugs exhibits:

negatives
positives
zeros
nulls.

Apologies for the confusion.

Jamie.

--

 

[Dirk Goldgar]

The DECIMAL bug (FWIW fixed for Access2007) only exhibits itself when
the order is descending and even then doesn't intermingle negative and
positive values.

True, but text sorting wouldn't exhibit the posted sort order either, so
I didn't trust that as an accurate representation of the sorted results.