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

"Bob" <(E-Mail Removed)> wrote in message news:ejar5o$42h$(E-Mail Removed)
> 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?

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

(please reply to the newsgroup)

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

"Bob" wrote:

>
>
> 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
>
>
>

"KARL DEWEY" <(E-Mail Removed)> wrote in message
news:64AA3497-D0E6-4824-8D4A-(E-Mail Removed)
> 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.

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

(please reply to the newsgroup)

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "KARL DEWEY" <(E-Mail Removed)> wrote in message
> news:64AA3497-D0E6-4824-8D4A-(E-Mail Removed)
>> 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.
>
> --
> 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" <(E-Mail Removed)> wrote in message news:ejatvh$8pt$(E-Mail Removed)...
>
> "Dirk Goldgar" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> "KARL DEWEY" <(E-Mail Removed)> wrote in message
>> news:64AA3497-D0E6-4824-8D4A-(E-Mail Removed)
>>> 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.
>>
>> --
>> 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
>
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

"Bob" <(E-Mail Removed)> wrote in message news:ejatvh$8pt$(E-Mail Removed)
>>
> 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.

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

(please reply to the newsgroup)

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

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> "Bob" <(E-Mail Removed)> wrote in message news:ejatvh$8pt$(E-Mail Removed)
>>>
>> 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.
>
> --
> 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" <(E-Mail Removed)> wrote in message news:ejb13u$eig$(E-Mail Removed)...
>
> "Dirk Goldgar" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> "Bob" <(E-Mail Removed)> wrote in message news:ejatvh$8pt$(E-Mail Removed)
>>>>
>>> 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.
>>
>> --
>> 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
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