Results 1 to 10 of 10

Thread: Sorting (2003)

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

    Re: Sorting (2003)

    I don't see a screenshot <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sorting (2003)

    <P ID="edit" class=small>(Edited by robm on 11-Jan-08 11:10. Screenshot added)</P>The attatched, is a screenshot of a subform. I want the records to be in order of Next Due, but as you can see they're not in order. The 01/0108 should be at the bottom. I have been in both the form and the table, and sorted them, but once the form is closed, and re-opened, they're mixed up again. Any suggestions would be welcome.
    Attached Images Attached Images

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

    Re: Sorting (2003)

    You must set the Record Source of the subform to a query that sorts the records on Next Due.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting (2003)

    Thanks Hans, that worked, but I've got a related problem. We have a checkbox on the form to show if the item is External, and also at the side of the record, to show when the calibration is complete. The problem I now have is that when I run a report, from a query, to show all External items, using MaxDate Due, the report is showing the last due date for one of the items, not all of them. Say I have an item FND001 due on 01/03/07, which was done, and ticked complete. The next due date is 01/02/08. When I run the report to show all External Item, I would expect to see 01/02/08, but instead I get 01/03/07.

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

    Re: Sorting (2003)

    I'd have to know (much) more about the query/queries you're using before I can help.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Sorting (2003)

    Does the query that finds Max Datedue have a groupby field?
    Without it you will get the overall Max, but if you group by items you will get a separate Max for each item.
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting (2003)

    Yes, the query does have a group by. I've attatched a cut down version of the database. The qryExternal should show 2 items and not 3, whether they're complete or not. The query External Outstanding is working ok, showing only 1 outstanding item. It's probably something obvious that I'm missing.
    Attached Files Attached Files

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

    Re: Sorting (2003)

    The field Serial No: in the Calibration Detail table should *not* be an AutoNumber field - it contains duplicate values.

    Remove the Completed column from both qryCalibrationDue1 and qryExternal. You'll then get 2 records (you now get 3 because you also group on Completed).

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting (2003)

    That's bad news. I've got 670 records in the original details table. I created a blank table and set serial No to Number, the data won't append as it says there is a mismatch.

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

    Re: Sorting (2003)

    First, delete the relationship between tblCalibration_Main and tblCalibration_Detail.
    Open tblCalibration_Detail in design view.
    Change the data type of Serial No: from AutoNumber to Number.
    Close and save the table.
    Then create the relationship again.

Posting Permissions

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