Results 1 to 7 of 7
  1. #1
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81

    Exclamation Module for most recent date

    Hello all,



    I'm trying to write a module that calculates the most recent date of three dates per record. There may be some blanks and some records will have blanks for all three fields:

    Record Date1 Date2 Date3 Desired Output
    A 01/03/2019 01/04/2019 01/05/2019 01/05/2019
    B 01/05/2018 01/03/2017 01/05/2016 01/05/2018
    C null 01/06/2019 01/07/2018 01/06/2019
    D null 01/07/2020 null 01/07/2020
    E null null null null


    I have made a start writing a module (below) that will work on two dates but that doesn't seem to work. Can anyone help please?


    Code:
    Public Function GetMaxDate2(dteDate1 As Date, dteDate2 As Date) As Date
    
        Dim dteMaxDate As Date
        
        'dteMaxDate = dteDate1
        If (dteDate1 Is Null And dteDate2 Is Null) Then
        dteMaxDate = Null
        ElseIf dteDate1 Is Null Then
        dteMaxDate = dteDate2
        ElseIf dteDate2 Is Null Then
        dteMaxDate = dteDate1
        ElseIf dteDate2 > dteDate1 Then
        dteMaxDate = dteDate2
        Else
        dteMaxDate = dteDate1
        
        GetMaxDate2 = dteMaxDate
        
    End If
    
    
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I dont see the need to submit the 2 date params.
    are you wanting to return 1 Max date for 1 record Key (like Record B)?
    or
    set MaxDate for ALL records?


    if 1 return, you'd submit the Key:

    Code:
    Public Function GetMaxDate2(byval pvKey)
    dim rst
    dim vMax
    dim fld as field
    
    on error resume next
    
    set rst = currentdb.openrecordset("select * from table where [recordkey]='" & pvKey & "'")
    with rst
      while not .eof
       for each fld in .fields
          if isdate(fld.value) and fld.value > vMax then vMax = fld.value
       next
      .movenext
    end with
    
    GetMaxDate2 = vMax
    set rst = nothing
    set fld = nothing

  3. #3
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Hi there,

    Thanks for that code. I'm only a beginner and I'm struggling to decipher what it is actually doing

    I copied it into a new module and tried to call it from a query without success.

    I've attached an excel zip with an example table which might illustrate what I'm trying to achieve. Also there may be other date fields in the table which I don't want to include in the calculation.

    Thankyou!
    Attached Files Attached Files

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Also there may be other date fields in the table which I don't want to include in the calculation.
    Without knowing what that means, I think it would be pointless to work out something that gives you the max date over a set of fields, only to be told you don't want certain fields. However if you want to take a crack at it, maybe UNION the values from a record (as a recordset) and use DMax over that recordset. I don't see why you're only comparing 2 dates when you show 3, and in at least one desired case you've picked the 3rd?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You have this issue as your data is not normalized.

    ranman256's code is a good solution, but if you have other fields in the record, check that the first 4 characters of the fieldname is Date before doing the other checks.

  6. #6
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Thankyou all for your help and sorry if my explanations weren’t easy to understand.

    I found a solution to what I needed:

    http://www.allenbrowne.com/func-09.html
    Returns the latest of several dates in a record:

    Code:
    Function MinOfList(ParamArray varValues()) As Variant
        Dim i As Integer        'Loop controller.
        Dim varMin As Variant   'Smallest value found so far.
    
        varMin = Null           'Initialize to null
    
        For i = LBound(varValues) To UBound(varValues)
            If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
                If varMin <= varValues(i) Then
                    'do nothing
                Else
                    varMin = varValues(i)
                End If
            End If
        Next
    
        MinOfList = varMin
    End Function
    
    ----
    
    Function MaxOfList(ParamArray varValues()) As Variant
        Dim i As Integer        'Loop controller.
        Dim varMax As Variant   'Largest value found so far.
    
        varMax = Null           'Initialize to null
    
        For i = LBound(varValues) To UBound(varValues)
            If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
                If varMax >= varValues(i) Then
                    'do nothing
                Else
                    varMax = varValues(i)
                End If
            End If
        Next
    
        MaxOfList = varMax
    End Function

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks for posting your solution.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Second to Most Recent Date
    By coralis in forum Access
    Replies: 4
    Last Post: 07-10-2019, 02:53 AM
  2. Most recent date
    By OCStan in forum Forms
    Replies: 4
    Last Post: 10-20-2016, 10:45 AM
  3. Replies: 1
    Last Post: 10-02-2015, 12:28 PM
  4. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  5. Choose the row with the MOST RECENT date
    By taimysho0 in forum Queries
    Replies: 1
    Last Post: 05-14-2012, 02:35 PM

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
  •  
Other Forums: Microsoft Office Forums