Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Nested IF AND OR

    I have been scratching my head on this one all day.

    I have 3 date columns [date 1], [date 2] and [date 3]

    I want the the highest value (or most recent date) of the 3 dates to update a another column in the dbase.

    Problem is that all 3 dates have some null values and in that case I want to return the highest value between the not nulls.

    If [date 1] is null, return the highest value between [date 2] and [date 3]
    If [date 2] is null, return the highest value between [date 1] and [date 3]
    If [date 3] is null, return the highest value between [date 1] and [date 2]
    If two date columns are null, return the value which is not null
    If all 3 are null do not update the column

    any help will be most appreciated

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Code:
    
    Public Function GetBigDate(pvDate1, pvDate2, pvDate3)
    Select Case True
        Case IsNull(pvDate1) and     IsNull(pvDate2) and     IsNull(pvDate3)
              getbigdate = ""
        Case IsNull(pvDate1)  and     IsNull(pvDate2) and    not IsNull(pvDate3)
            .... etc
    
        Case IsNull(pvDate1)
                'return the highest value between [date 2] and [date 3]
            If pvDate2 > pvDate1 Then
               GetBigDate = pvDate2
            Else
               GetBigDate = pvDate1
            End If
         
        Case IsNull(pvDate2)
             'return the highest value between [date 1] and [date 3]
            If pvDate1 > pvDate3 Then
               GetBigDate = pvDate1
            Else
               GetBigDate = pvDate3
            End If
         
        Case IsNull(pvDate3)
             'return the highest value between [date 1] and [date 2]
            If pvDate1 > pvDate2 Then
               GetBigDate = pvDate1
            Else
               GetBigDate = pvDate2
            End If
    End Select

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    brilliant thank you

Posting Permissions

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