# Thread: Nested IF AND OR

1. Registered User
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. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
629
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. Registered User
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
•