Sorting in the report with in the group

[Guest]

My report is grouped by date and I am trying to sort the records Descending
by sales amount with in the group, however my negatives show up on the top,
is there anyway to drop them to the bottom?

Thanks

 

[Allen Browne]

If you use the Sorting And Grouping dialog (View menu in report design), you
have the option to sort Descending.

If you have already done that, but the report is not sorting correctly,
there are a couple of possible causes:

a) The field is a Decimal data type, and Access is incapable of sorting that
type correctly. Details in:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

b) The field is a calculated field, and Access is misunderstanding the data
type. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Either way, the solution is probably to typecast the field in your query,
e.g.:
SELECT CCur(Nz(Sum([Amount]),0)) As SumOfAmount ...

 

[David Lloyd]

Have you added the Sales Amount field to the Sorting and Grouping dialog box
and chosen Descending as the sort order type?

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


My report is grouped by date and I am trying to sort the records Descending
by sales amount with in the group, however my negatives show up on the top,
is there anyway to drop them to the bottom?

Thanks

 

[Guest]

Thank you. Now, yes I looked at the field type and it is decimal. The data
comes from as400 db. My query qry_make_invoice_05_tbl retrieves the data from
as400, then creates tbl_inoiced_05, if I change the field type to generic
number in the qry would that help ? Or there something else I should do?
Should I create a tabla with my own field types and then just have a qry that
appends the values?

Thank you fo all your help.

--
If at first you don''''t succeed, destroy all evidence that you tried.

If you use the Sorting And Grouping dialog (View menu in report design), you
have the option to sort Descending.

If you have already done that, but the report is not sorting correctly,
there are a couple of possible causes:

a) The field is a Decimal data type, and Access is incapable of sorting that
type correctly. Details in:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

b) The field is a calculated field, and Access is misunderstanding the data
type. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Either way, the solution is probably to typecast the field in your query,
e.g.:
SELECT CCur(Nz(Sum([Amount]),0)) As SumOfAmount ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My report is grouped by date and I am trying to sort the records
Descending
by sales amount with in the group, however my negatives show up on the
top,
is there anyway to drop them to the bottom?

Thanks

 

[Guest]

So far I came out with the double transfer, but this is the way I do it:
export query to excel, then import back to the db, then it comes in as
Double instead of Decimal, and it sorts properly. Now if there is an easier
way, please let me know. Thanks.
--
If at first you don''''t succeed, destroy all evidence that you tried.

Thank you. Now, yes I looked at the field type and it is decimal. The data
comes from as400 db. My query qry_make_invoice_05_tbl retrieves the data from
as400, then creates tbl_inoiced_05, if I change the field type to generic
number in the qry would that help ? Or there something else I should do?
Should I create a tabla with my own field types and then just have a qry that
appends the values?

Thank you fo all your help.

--
If at first you don''''t succeed, destroy all evidence that you tried.

If you use the Sorting And Grouping dialog (View menu in report design), you
have the option to sort Descending.

If you have already done that, but the report is not sorting correctly,
there are a couple of possible causes:

a) The field is a Decimal data type, and Access is incapable of sorting that
type correctly. Details in:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

b) The field is a calculated field, and Access is misunderstanding the data
type. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Either way, the solution is probably to typecast the field in your query,
e.g.:
SELECT CCur(Nz(Sum([Amount]),0)) As SumOfAmount ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My report is grouped by date and I am trying to sort the records
Descending
by sales amount with in the group, however my negatives show up on the
top,
is there anyway to drop them to the bottom?

Thanks

 

[Allen Browne]

Typecast the field in the query, so JET treats it as currency or double or
something else.

You will end up with something like this in the Field row in query design:
CCur(Nz([YourDecimalFieldNameHere],0))

Use CDbl() to get a Double.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

So far I came out with the double transfer, but this is the way I do it:
export query to excel, then import back to the db, then it comes in as
Double instead of Decimal, and it sorts properly. Now if there is an
easier
way, please let me know. Thanks.
--
If at first you don''''t succeed, destroy all evidence that you tried.

Thank you. Now, yes I looked at the field type and it is decimal. The
data
comes from as400 db. My query qry_make_invoice_05_tbl retrieves the data
from
as400, then creates tbl_inoiced_05, if I change the field type to generic
number in the qry would that help ? Or there something else I should do?
Should I create a tabla with my own field types and then just have a qry
that
appends the values?

Thank you fo all your help.

--
If at first you don''''t succeed, destroy all evidence that you tried.

If you use the Sorting And Grouping dialog (View menu in report
design), you
have the option to sort Descending.

If you have already done that, but the report is not sorting correctly,
there are a couple of possible causes:

a) The field is a Decimal data type, and Access is incapable of sorting
that
type correctly. Details in:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

b) The field is a calculated field, and Access is misunderstanding the
data
type. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Either way, the solution is probably to typecast the field in your
query,
e.g.:
SELECT CCur(Nz(Sum([Amount]),0)) As SumOfAmount ...

My report is grouped by date and I am trying to sort the records
Descending
by sales amount with in the group, however my negatives show up on
the
top,
is there anyway to drop them to the bottom?