Results 1 to 6 of 6

Thread: Max Date

  1. #1
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think you will need to post an example.
    Just some example data from the table and what you want on the form.
    Andrew

  3. #3
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am waiting on some more information and then I will post .Thanks

  4. #4
    Lounger
    Join Date
    Dec 2009
    Location
    White Bear Lake, MN
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.sub formName.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.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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)

    [attachment=89117:Max Dates.zip]

    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.
    Attached Files Attached Files
    Andrew

  6. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •