Max Date

Topic

New Reply

I have a situation where I have a database called issues and the main form will be the issue and when it started and the subform called resolution activities where a person/people are trying to solve the issue. There are some 3 or 4 date/time fields within the form and subform but I need a field on the main form and a report that shows the last date something was done to the issue. So I need to find the max date of 3 or 4 fields to place on the main form and to calulcate in a query. I know how to calculate the maxdate on 1 field but not many fields. Is this scenario possible and if so how? Thanks

Reply | Quote

Replies

I think you will need to post an example.
Just some example data from the table and what you want on the form.

Reply | Quote

I am waiting on some more information and then I will post .Thanks

Reply | Quote

You need to let us know what FE you’re using. Looks like Access. Also are there three or are there four date/time fields in the subform?

Here is a start. read help on IIF and you should be able to complete this on your own.
put a textbox on the main form.
in the textbox source put: =IIF(formName.subformName.date1ControlName > formName.subformName.date2ControlName, formName.subformName.date1ControlName,formName.subformName.date2ControlName)

This will give you the newest date of 2 controls. I don’t know what will happen if one or both of them is NULL. Post the results if it works or not.

Reply | Quote

It might also be worth having a look at a Custom User defined function for this.
Although they do tend to execute more slowly with big data sets.

Have a look at Allen Brownes MaxOfList and MinOfList Functions

Yoy could use these to pass in the Max of each field using a Group By Query and it ought to return the Maximum of the Maximums so to speak

See Functions HERE

Also in Example DB attached (note this is in Access)

I have done the Calculation in a Query, BUT no reason you could not use it and pass it the Max Values from fields on a Sub Form.

Reply | Quote

Here is a custom function that gets the max date from a list of any length. Just keep adding arguments. It handles nulls and invalid dates as well.

Code:

Public Function MaxOfDate(ParamArray dates() As Variant) As Date
    Dim MaxDate As Date, i As Integer
    
    MaxDate = 0
    For i = 0 To UBound(dates)
        If (IsDate(dates(i))) Then
            If (Nz(dates(i), 0) > MaxDate) Then MaxDate = dates(i)
        End If
    Next i
    MaxOfDate = MaxDate
    Exit Function
End Function

An example call would be: MaxDate = MaxOfDates (DateField1, DateField 2, DateField3, DateField4, … )

This works best in a form where you have only one record that is being evaluated, but it can work in a query as well, but, as AKW mentioned, it will be slower. In a query, I’d use nested iif statements if this became too slow.

Reply | Quote