using the greater of 4 dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
Did you try the function that I posted?

--

Ken Snell
<MS ACCESS MVP>

dchristo said:
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 said:
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
Click to expand...
Click to expand...
 
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,Marketable_Title_Date,Date_Foreclosure_Sale)
xlngUB =
UBound(First_Known_Vacancy,Redemption_Period,Marketable_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 said:
Did you try the function that I posted?

--

Ken Snell
<MS ACCESS MVP>

dchristo said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Just wanted to say Thank You, this works great

Ken Snell said:
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
Click to expand...
 
Back
Top