Results 1 to 6 of 6

Problem getting latest date

  1. #1
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    11

    Problem getting latest date


    We have data dumped into a table weekly, and we're running reports to see how long issues are taking to be resolved. I don't see what the issue is here, but a query is not returning the latest date (which was run 9/15). Instead, it returns 9/8. Here's a scaled down version of the query, and it's not working.

    SELECT Max(Problem.[DATE ENTERED]) AS MaxDateEntered, Problem.Name
    FROM Problem
    GROUP BY Problem.Name;


    Thanks for any guidance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,376
    That looks like values in a text field which sorts by alpha rules - one character at a time. 8 is greater than 1, therefore it is the latest. If you use text field, then need placeholder zeros.

    Given "09/08" and "09/15", "09/15" will sort as latest.

    The query is working properly, data is bad.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    11
    Gotcha. Thanks!

  4. #4
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    11
    The data type for this is actually Date/Time, and not text. Should be behavior be the same? Thanks

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,376
    A date/time field actually stores value as a long integer so no, should not act like text. But where is the year component for your example values?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    albaker is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    11
    My bad. You were exactly correct. I have multiple tables, and this is the only one that had the date/time info as a text. It's working. Thanks a bunch.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter latest date of a value
    By ratercero in forum Queries
    Replies: 7
    Last Post: 05-31-2017, 01:27 PM
  2. latest date
    By slimjen in forum Programming
    Replies: 11
    Last Post: 09-12-2014, 08:46 PM
  3. Value of latest date
    By v!ctor in forum Queries
    Replies: 3
    Last Post: 02-27-2013, 03:48 PM
  4. How do you get the latest date?
    By radicrains in forum Queries
    Replies: 6
    Last Post: 10-27-2010, 08:59 PM
  5. Latest Date Records
    By Rick West in forum Queries
    Replies: 1
    Last Post: 09-25-2009, 11:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums