Results 1 to 8 of 8
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Query sort is unpredictable

    I really thought this would be pretty simple . . .

    I have a query with a date entry and a time entry. I want to sort first by date and then by time in descending order:

    8/1/2011 14:23
    8/1/2011 13:17
    8/1/2011 11:05
    7/7/2011 16:18

    It's not doing that. It sorts by date in descending order just fine, but the time results are really unpredictable. Here is something like the results I get:

    8/1/2011 13:17
    8/1/2011 14:23
    8/1/2011 11:05
    7/7/2011 16:18

    I have both fields set as "descending" in the sort setting. How difficult can this be? I even tried this in the report using group/sort and get the same kind of (unpredictable) time results.
    8-7-2011 5-51-57 PM.jpg
    Last edited by bfxtrfcmgr; 2011-08-07 at 20:53.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by bfxtrfcmgr View Post
    I really thought this would be pretty simple . . .
    I agree. What you are doing looks OK assuming the Date field really contains what you think it does.
    Date and Time fields are the same data type, with whole numbers representing the days and the decimal part the time.

    So I am wondering if your Dates have something (perhaps hidden) represeting a Time in them. How does the data get into the Date field?
    Do you have a default value? What is it? (e.g. if the Default was Now() Access would put the current time into the data field.)

    Can you perhaps post a bit of sample data?
    Regards
    John



  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    The default values are Date() and Time()Sample date from query attached
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Can you post the actual sample data and your query rather than just a picture of it?
    Regards
    John



  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    According to Access 2007 help the sort order is as I suspected; it sorts the first field first, the second field second, the thir . . . .But that's not what I'm getting (all the time anyway). Check out these expanded results; everything looks good until you hit 7/20, and then it looks OK until 7/13. If I run the query again, the results remain the same. I also checked the input mask and found that it is "Short Time", but no input mask for date because the Date Picker is utilized.
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try changing the format of the Date field to mm/dd/yyyy h:nn.

    This will show you if there are actually times in there after the dates.
    Regards
    John



  7. The Following User Says Thank You to johnhutchison For This Useful Post:

    bfxtrfcmgr (2011-08-07)

  8. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by johnhutchison View Post
    Can you post the actual sample data and your query rather than just a picture of it?
    Not in any way to discount your request John, but I found the problem. The default values were just as I described and I thought the only input method was by the default value in the table settings, but I have since discovered FrmJournalNew where, as you suspected, the default value is Now(). Going through the records I see (by clicking in the cell) where the vast majority are short time, but that some contain date and time to the second. There's only a few hundred records to sort through so I'll just edit manually and disable the date and time entry in the form.Thanks for the help!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  9. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad you found it.
    Regards
    John



Posting Permissions

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