using the greater of 4 dates

I have 4 date fields (one of the dates could be empty) on my form - I want to
prepopulate another field with the greater of the 4 dates. Can someone tell
me how to accomplish this

Function HighDate(dtmDate1 As Date, dtmDate2 As Date, dtmDate3 As Date, _
dtmDate4 As Date) As Date
Dim varDates As Variant
Dim intX As Integer
Dim dtmHighDate As Date

varDates = Array(dtmDate1, dtmDate2, dtmDate3, dtmDate4)

For intX = 0 To 3
If varDates(intX) dtmHighDate Then
dtmHighDate = varDates(intX)
End If
Next intX

HighDate = dtmHighDate

End Function


"dchristo" wrote:

I have 4 date fields (one of the dates could be empty) on my form - I want to
prepopulate another field with the greater of the 4 dates. Can someone tell
me how to accomplish this

Dim DATE1 As Date, DATE2 As Date, DATE3 As Date, HIGHDATE As Date
DATE1 = Forms!Form1.[Date1]
DATE2 = Forms!Form1.[Date2]
DATE3 = Forms!Form1.[Date3]

If DATE1 HIGHDATE Then HIGHDATE = DATE1
If DATE2 HIGHDATE Then HIGHDATE = DATE2
If DATE3 HIGHDATE Then HIGHDATE = DATE3
Forms!Form1.[highdate]=HIGHDATE


thats all

--
Message posted via http://www.accessmonster.com

Here is a generic function that will accept any number of values and return
the maximum of them:


Public Function MaxValueVariantArray(ParamArray ValuesArray() As Variant) As
Variant

' ** THIS FUNCTION RETURNS THE MAXIMUM VALUE FROM AN ARRAY OF VALUES.
' Ken Snell 19 May 2005

Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB = 0 And xlngLB = 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) xvarTp Then xvarTp = ValuesArray(xlngCnt)
Next xlngCnt
End If
MaxValueVariantArray = xvarTp
End Function

--

Ken Snell
MS ACCESS MVP

"dchristo" wrote in message
...
I have 4 date fields (one of the dates could be empty) on my form - I want
to
prepopulate another field with the greater of the 4 dates. Can someone
tell
me how to accomplish this

Ok, I am having difficulty making this work, I have a text box that I want to
prepopulate with formula that you have given me. But when it put in before
update I does not work, Can you please help me out? also, every date but the
Date Foreclosure Sale is showing up 12:00 AM

Private Sub Text335_BeforeUpdate(Cancel As Integer)
Dim First_Time_Vacancy As Date, Redemption_Date As Date,
Marketable_Title_Date As Date, Date_Foreclosure_Date As Date, HIGHDATE As Date
First_Time_Vacancy = Forms!Borrower_Information.[First Time Vacancy]
Redemption_Date = Forms!Borrower_Information.[Redemption Date]
Marketable_Title_Date = Forms!Borrower_Information.[Marketable Title Date]
Date_Foreclosure_Sale = Forms!Borrower_Information.[Date Foreclosure Sale]


If First_Time_Vacancy HIGHDATE Then HIGHDATE = First_Time_Vacancy
If Redemption_Date HIGHDATE Then HIGHDATE = Redemption_Date
If Marketable_Title_Date HIGHDATE Then HIGHDATE = Marketable_Title_Date
If Date_Foreclosure_Sale HIGHDATE Then HIGHDATE = Date_Foreclosure_Sale
Forms!Borrower_Information.[HIGHDATE] = HIGHDATE
End Sub



"dchristo" wrote:

I have 4 date fields (one of the dates could be empty) on my form - I want to
prepopulate another field with the greater of the 4 dates. Can someone tell
me how to accomplish this

Did you try the function that I posted?

--

Ken Snell
MS ACCESS MVP

"dchristo" wrote in message
...
Ok, I am having difficulty making this work, I have a text box that I want
to
prepopulate with formula that you have given me. But when it put in
before
update I does not work, Can you please help me out? also, every date but
the
Date Foreclosure Sale is showing up 12:00 AM

Private Sub Text335_BeforeUpdate(Cancel As Integer)
Dim First_Time_Vacancy As Date, Redemption_Date As Date,
Marketable_Title_Date As Date, Date_Foreclosure_Date As Date, HIGHDATE As
Date
First_Time_Vacancy = Forms!Borrower_Information.[First Time Vacancy]
Redemption_Date = Forms!Borrower_Information.[Redemption Date]
Marketable_Title_Date = Forms!Borrower_Information.[Marketable Title Date]
Date_Foreclosure_Sale = Forms!Borrower_Information.[Date Foreclosure Sale]


If First_Time_Vacancy HIGHDATE Then HIGHDATE = First_Time_Vacancy
If Redemption_Date HIGHDATE Then HIGHDATE = Redemption_Date
If Marketable_Title_Date HIGHDATE Then HIGHDATE = Marketable_Title_Date
If Date_Foreclosure_Sale HIGHDATE Then HIGHDATE = Date_Foreclosure_Sale
Forms!Borrower_Information.[HIGHDATE] = HIGHDATE
End Sub



"dchristo" wrote:

I have 4 date fields (one of the dates could be empty) on my form - I
want to
prepopulate another field with the greater of the 4 dates. Can someone
tell
me how to accomplish this

Well to be honest, I am totally confused now. This is what I have, but I am
clueless here, can you help me out?

Private Sub Text342_BeforeUpdate(Cancel As Integer)
Public Function MaxValueVariantArray(First_Known_Vacancy, Redemption_Period,
Marketable_Title_Date, Date_Foreclosure_Sale) As Variant
Dim xlngLB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB =
LBound(First_Known_Vacancy,Redemption_Period,Marke table_Title_Date,Date_Foreclosure_Sale)
xlngUB =
UBound(First_Known_Vacancy,Redemption_Period,Marke table_Title_Date,Date_Foreclosure_Sale)
If xlngUB = 0 And xlngLB = 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) xvarTp Then xvarTp -ValuesArray(xlngCnt)
Next xlngCnt
End If
MaxValueVariantArray = xvarTp
End Function




"Ken Snell [MVP]" wrote:

Did you try the function that I posted?

--

Ken Snell
MS ACCESS MVP

"dchristo" wrote in message
...
Ok, I am having difficulty making this work, I have a text box that I want
to
prepopulate with formula that you have given me. But when it put in
before
update I does not work, Can you please help me out? also, every date but
the
Date Foreclosure Sale is showing up 12:00 AM

Private Sub Text335_BeforeUpdate(Cancel As Integer)
Dim First_Time_Vacancy As Date, Redemption_Date As Date,
Marketable_Title_Date As Date, Date_Foreclosure_Date As Date, HIGHDATE As
Date
First_Time_Vacancy = Forms!Borrower_Information.[First Time Vacancy]
Redemption_Date = Forms!Borrower_Information.[Redemption Date]
Marketable_Title_Date = Forms!Borrower_Information.[Marketable Title Date]
Date_Foreclosure_Sale = Forms!Borrower_Information.[Date Foreclosure Sale]


If First_Time_Vacancy HIGHDATE Then HIGHDATE = First_Time_Vacancy
If Redemption_Date HIGHDATE Then HIGHDATE = Redemption_Date
If Marketable_Title_Date HIGHDATE Then HIGHDATE = Marketable_Title_Date
If Date_Foreclosure_Sale HIGHDATE Then HIGHDATE = Date_Foreclosure_Sale
Forms!Borrower_Information.[HIGHDATE] = HIGHDATE
End Sub



"dchristo" wrote:

I have 4 date fields (one of the dates could be empty) on my form - I
want to
prepopulate another field with the greater of the 4 dates. Can someone
tell
me how to accomplish this

Just wanted to say Thank You, this works great

"Ken Snell [MVP]" wrote:

Here is a generic function that will accept any number of values and return
the maximum of them:


Public Function MaxValueVariantArray(ParamArray ValuesArray() As Variant) As
Variant

' ** THIS FUNCTION RETURNS THE MAXIMUM VALUE FROM AN ARRAY OF VALUES.
' Ken Snell 19 May 2005

Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB = 0 And xlngLB = 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) xvarTp Then xvarTp = ValuesArray(xlngCnt)
Next xlngCnt
End If
MaxValueVariantArray = xvarTp
End Function

--

Ken Snell
MS ACCESS MVP

"dchristo" wrote in message
...
I have 4 date fields (one of the dates could be empty) on my form - I want
to
prepopulate another field with the greater of the 4 dates. Can someone
tell
me how to accomplish this

You're welcome...

--

Ken Snell
MS ACCESS MVP

"dchristo" wrote in message
...
Just wanted to say Thank You, this works great

"Ken Snell [MVP]" wrote:

Here is a generic function that will accept any number of values and
return
the maximum of them: