Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Unanswered: Sorting Anomalies

    Why is that Query Sorts and Report Sorts sometimes produce anomalous results? For example, I have an inventory database in which the sort ascending by vendors produces a strange result, ie. All the vendors starting with B... come first then A, C, D, E .... etc all in the correct order. There is no leading hidden character or blank space. I have another client who needs to sort transactions by date and everything seems to work fine except October comes first then Jan, Feb, March, etc.

    So what happens that causes these strange occurrences?

    Terry Hoffer

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Check the data type for the date field. If it's "text", that would explain the sort order.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    silly thought!
    are you sorting what you think you are sorting?

    for e.g. if your field in tblPurchases is a lookup to tblVendors and the actual data stored is the PK of the tblVendors entry but you have hidden the key field and you see the name of the vendor... what SHOULD access sort on if you sort on the lookup in tblPurchases: you can make a good case for both possibilities.

    i must be into my n-millionth sort by now and i haven't seen an access error yet (different story with Excel by the way!)

    izy

  4. #4
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29
    Originally posted by JTRockville
    Check the data type for the date field. If it's "text", that would explain the sort order.
    The date items are in access's date/time format, and the vendor names are text.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    all that's left is to zip'n'post something that doesn't work.

    izy

  6. #6
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179

    Thumbs up

    I vote zip 'n post too.

    I can't imagine why you're getting those results - but then again, my imagination could use a little jump-start this morning.

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    try this - If you are sorting based on a query (in afor or report) use that query as a maketable query and use the new table as underlying source for the report - this should work.

    There are Microsoft articless that explain why a query and report do not sort the same way on the same data.

    So sort the query and create a table with it - this should solve the problem

    I only reccommend this if we are not talking 500k records.

    Also remember - a maketable query will write the table to the frontend db if this is a split database and other users will not see the table or be able to run the report, but they can run the procedure as well and then run the report.

    I hope this makes sense.

    Weird behavior but this is a workaround.

Posting Permissions

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