Help With A Statement For A Date Field

[jamason07]

Hi!

I have a form which has 4 fields called "Date1", "Date2", "Date3" and "Date4". These dates are calculated in a query based on user inputs. I have another field called "DueDate". I need the "DueDate" field to display the highest value of the four "Date" fields. Any suggestions on a statement to associate with the "DueDate" field to accomplish this? Thanks!

JAM

 

[Golom]

You probably need to write a VB function to return the max date like this

' ===============================================================================
' Return the maximum value of the supplied values
' ===============================================================================
Public Function MaxVal(ParamArray Vals() As Variant) As Variant
    Dim x As Variant
    Dim MV As Variant
    MV = Vals(0)
    For Each x In Vals
        If x > MV Then MV = x
    Next
    MaxVal = MV
    
End Function

Then use it in your SQL

Select ..., MaxVal([Date1], [Date2], [Date3], [Date4]) As DueDate, ...

 

[BobStubbs]

Try this:

Add this procedure to the 'General' section of VB for your form:

Sub ShowHighestDate()
Dim dtHighestDate As Date

If IsDate(Date1) Then
dtHighestDate = Date1
End If

If date2 > dtHighestDate Then
dtHighestDate = date2
End If

If date3 > dtHighestDate Then
dtHighestDate = date3
End If

If date4 > dtHighestDate Then
dtHighestDate = date4
End If

If dtHighestDate > "00:00:00" Then
dateHighest = dtHighestDate
Else
dateHighest = ""
End If
End Sub

Now call this procedure from the 'on lost focus' event of each of the four date fields. For example:

Private Sub date1_LostFocus()
ShowHighestDate
End Sub

Each time you type a date in one of the four fields, and move the cursor out of the field, this code checks the four dates and changes the DueDate field if required.

I have checked this with various combinations of dates and blank fields, and it works OK for me.

I hope that this helps ...


Bob Stubbs

 

[jamason07]

Thanks Bob...but where do I attach this to the "DueDate" field?

 

[BobStubbs]

My fault! In my example, I called the output field 'dateHighest'. At the end of my original example, write 'DueDate' instead, if that is your output field name:

If dtHighestDate > "00:00:00" Then
DueDate = dtHighestDate
Else
DueDate = ""
End If

The code doesn't need to be attached to the DueDate field at all. It is triggered by the LostFocus event of any of the four individual date fields - i.e. when the cursor tabs out of one of these fields.

Sorry for any confusion!



Bob Stubbs