Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Order is Wrong (2000)

    I am trying to sort a list of records in a report by an Amount field, in descending order, so that the record with the highest value in its amount field appears at the top of the list. This is a simple thing, and I have done it many times before, but this time it just doesn't work. All the positive numbers order correctly, with the largest at the top. However, the table contains one record with a zero in the Amount field, and one with a negative number. The zero comes first, then the negative number, then the positive numbers, ordered as expected. e.g.:

    0.00
    -170.00
    8,700.00
    7,071.00
    5,000.00
    etc.

    The records come from a work table that is created by running a make table query before the report is run. Access creates the Amount field with a data type of Decimal, precision 28, scale 2. I intend to use the same report to display the data sorted in a number of different ways, so the sort order is set in the Open event of the report according to the value of a hidden field on the calling form:

    strForm = "frm_OpKPIReports"
    If CurrentProject.AllForms(strForm).IsLoaded Then
    strSortField = Forms(strForm).txtOrderBy
    Me.OrderBy = strSortField & " DESC"
    Me.OrderByOn = True
    End If

    The resulting order by text in the report's properties is: 'Amount DESC' (without the quotes).
    That looks right, so I opened the table and sorted it using the button on the toolbar. This produced the same result as that shown above (zero, then a negative number, then the positives in descending order).
    I tried indexing the table on the amount column, but this did not help.
    I am stumped. Is there something about the Decimal field type that I need to know perhaps?

    Thanks

    Ian

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Order is Wrong (2000)

    The sorting you have now is a text sort.
    If your field is named Amount use in the underlying query of the form :
    NrAmount : Val([Amount])
    to make Amount numeric and use NrAmount as control source for your textbox.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Order is Wrong (2000)

    I've done a quick test and I think that works, thank you. But can you tell me why? I thought decimal fields were already numeric. Where else am I going to have to do this, I wonder?

    Ian

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Order is Wrong (2000)

    Is your field the result of a format function ? This is always treated as a string.
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Order is Wrong (2000)

    If the answer to Francois' question is no, the problem may be in the Decimal data type. It is numeric, but it is not a native Access data type - Currency is the type to use for financial values. There have been threads in the past about Access treating Decimal fields incorrectly (for example <post#=311411>post 311411</post#>)

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Order is Wrong (2000)

    Sorry to take so long to reply. The table was created by a make table query from a SQL server table and the data type that Access chose to create all the non integer numeric fields was Decimal. I have since changed the field types to Currency and Double and now delete all the records in the table and append to it from the SQL server, rather than creating it from scratch each time, and the sorts now seem to work.

    If there are any details on other problems with the decimal type, I'd be interested.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Order is Wrong (2000)

    The post I referred to mentions another problem. I think it is wise to avoid the Decimal data type in Access tables, since it is not a "native" data type.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •