M
Mike
Group
I have a table with four fields that contain date data. I need to
compare and determine which one of the four dates is the largest. I have
tried to do some iif() statements but with mixed results. Most of the dates
work out of 158 records only 21 don't work. I thought I would recheck the
data so I went to the table and Reentered it. I figured there has to be an
easier way of doing this. Below is an outline of what I need to do.
'below is a fake example of the data I'm using. Showing the null problem.
date 1 date 2 date 3 date 4
"null" 12/05/2000 01/09/2001 5/04/2003
I think what I have to do is to create a function to do this. Maybe using a
case statement this is what i'm thinking below. I think this code will work
but I need to make it a function. I would like my function to be named
LargestDateOf4 and have the following syntax <"maybe wrong term here"
date1,date2,date3,date4. Then I would go into my query and call the function
up assigning date1 equal to a field in a table etc....
Thanks Mike Sundman
mike( d o t )sundman( a t )ctd1.( c o m )
Dim dtmLargestDate as Date
Dim varDate1 as Date
Dim varDate2 as Date
Dim varDate3 as Date
Dim varDate4 as Date
Dim Max1 as Date
Dim Max2 as Date
Dim dtmLargestDate as Date
'Tests data and enters place holder of 01/01/1901
IF isdate(date1) = False Then
varDate1 = #01/01/1901#
Else
varDate1 = date1
End If
IF isdate(date2) = False Then
varDate2 = #01/01/1901#
Else
varDate2 = date2
End If
IF isdate(date3) = False Then
varDate3 = #01/01/1901#
Else
varDate3 = date3
End If
IF isdate(date4) = False Then
varDate4 = #01/01/1901#
Else
varDate4 = date4
End If
'Compares Max1 and Max2 to return largest date.
If varDate1 > varDate2 then
Max1 = varDate1
Else
Max1 = varDate2
End If
If varDate3 > varDate4 then
Max2 = varDate3
Else
Max2 = varDate4
End If
If Max1 > Max2 then
dtmLargestDate = Max1
Else
dtmLargestDate = Max2
End If
End Function
End If
I have a table with four fields that contain date data. I need to
compare and determine which one of the four dates is the largest. I have
tried to do some iif() statements but with mixed results. Most of the dates
work out of 158 records only 21 don't work. I thought I would recheck the
data so I went to the table and Reentered it. I figured there has to be an
easier way of doing this. Below is an outline of what I need to do.
'below is a fake example of the data I'm using. Showing the null problem.
date 1 date 2 date 3 date 4
"null" 12/05/2000 01/09/2001 5/04/2003
I think what I have to do is to create a function to do this. Maybe using a
case statement this is what i'm thinking below. I think this code will work
but I need to make it a function. I would like my function to be named
LargestDateOf4 and have the following syntax <"maybe wrong term here"
date1,date2,date3,date4. Then I would go into my query and call the function
up assigning date1 equal to a field in a table etc....
Thanks Mike Sundman
mike( d o t )sundman( a t )ctd1.( c o m )
Dim dtmLargestDate as Date
Dim varDate1 as Date
Dim varDate2 as Date
Dim varDate3 as Date
Dim varDate4 as Date
Dim Max1 as Date
Dim Max2 as Date
Dim dtmLargestDate as Date
'Tests data and enters place holder of 01/01/1901
IF isdate(date1) = False Then
varDate1 = #01/01/1901#
Else
varDate1 = date1
End If
IF isdate(date2) = False Then
varDate2 = #01/01/1901#
Else
varDate2 = date2
End If
IF isdate(date3) = False Then
varDate3 = #01/01/1901#
Else
varDate3 = date3
End If
IF isdate(date4) = False Then
varDate4 = #01/01/1901#
Else
varDate4 = date4
End If
'Compares Max1 and Max2 to return largest date.
If varDate1 > varDate2 then
Max1 = varDate1
Else
Max1 = varDate2
End If
If varDate3 > varDate4 then
Max2 = varDate3
Else
Max2 = varDate4
End If
If Max1 > Max2 then
dtmLargestDate = Max1
Else
dtmLargestDate = Max2
End If
End Function
End If