Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Unanswered: Max Date of 5 Columns

    My goal is to get the max date from 5 different fields. These dates are completed dates for five different tasks associated with a project. At the moment I don't care so much about the task, but I want to know the last time any activity took place on a project. The field names are:
    BCDate
    OCDate
    SADate
    SCDate
    PCDate

    I am a new user to Access and I would appreciate any insight or assistance.

    Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have to compare each one individually.

    Or you could normalize your database.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2008
    Posts
    4
    Could you please provide an example?

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Without knowing anymore about your database, it sounds like you need a second and/or a third table. A table that stores Project Activities. It might have the following fields:

    ProjectID, ActivityID, Date

    Then an Activity table like:

    ActivityID, ActivityDescription

    The ProjectActivity table would have multiple records for each project depending on the activities associated with the project. This allows you to add 'unlimited' types of activities instead of just 5. Also, it allows you to answer your question using SQL.

    Forgive me if I am misunderstanding your table design. But to answer your question, the only way I can thinkg of to figure out which date is most recent you will have to create a function and call it from a query.

    Here is a function:
    Code:
    Public Function MostRecentDate(varDate1 As Variant, _
                                   varDate2 As Variant, _
                                   varDate3 As Variant, _
                                   varDate4 As Variant, _
                                   varDate5 As Variant) As Date
        
            Dim datMostRecent As Date
            
            If Nz(varDate1, 0) > datMostRecent Then datMostRecent = Nz(varDate1, 0)
            If Nz(varDate2, 0) > datMostRecent Then datMostRecent = Nz(varDate2, 0)
            If Nz(varDate3, 0) > datMostRecent Then datMostRecent = Nz(varDate3, 0)
            If Nz(varDate4, 0) > datMostRecent Then datMostRecent = Nz(varDate4, 0)
            If Nz(varDate5, 0) > datMostRecent Then datMostRecent = Nz(varDate5, 0)
        
            MostRecentDate = datMostRecent
            
    End Function
    Create a new module, copy the code to the module, then create a field in a query like this:

    MostRecent:MostRecentDate([BCDate],[OCDate],[SADate],[SCDate],[PCDate])

    That should give you the most recent date, but it won't tell you which field the date was from (assuming you are trying to report the activity that is associated with the date).

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or you could normalise the database and just Max the date column, as suggested by Teddy.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Max(the_date) As [max_date]
    FROM   (
            SELECT BCDate As [the_date]
            FROM   the_table
              UNION
            SELECT OCDate
            FROM   the_table
              UNION
            SELECT SADate
            FROM   the_table
              UNION
            SELECT SCDate
            FROM   the_table
              UNION
            SELECT PCDate
            FROM   the_table
           ) As [some_alias]
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2008
    Posts
    4
    Thanks so much to DCKunkle! That is exactly what I needed.

Posting Permissions

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