Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding the Next (Minimum) date (Access XP)

    A question that was asked of me, and I am forwarding to the group.

    I have a record in a table that has five different dates in it. Any one of these could be the next (minimum) date. The requestor would like an additional field on the form that displays the minimum value of the five date fields. Aside from redesigning the database, which is not an option here, what is the best method of having Access check all five dates and displaying the minimum date in another field?

    Thanks for any help provided.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding the Next (Minimum) date (Access XP)

    Put the following code in a standard module:

    Public Function FMin(ParamArray s() As Variant) As Variant
    ' Returns the minimum date in the list supplied
    Dim dtmMin As Date
    Dim i As Integer
    Dim varValue As Variant
    Dim intCount As Integer
    ' Dummy initial value
    dtmMin = #12/31/9999#
    For i = LBound(s) To UBound(s)
    varValue = s(i)
    If IsDate(varValue) Then
    If CDate(varValue) < dtmMin Then
    dtmMin = CDate(varValue)
    End If
    intCount = intCount + 1
    End If
    Next i
    If intCount = 0 Then
    FMin = Null
    Else
    FMin = dtmMin
    End If
    End Function

    This function will ignore all non-date values, including nulls. Use it like this in a query:

    NextDate: FMin([Date1], [Date2], [Date3], [Date4], [Date5])

    Or in the control source of a text box:

    =FMin([Date1], [Date2], [Date3], [Date4], [Date5])

    (Substitute the correct field names)

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the Next (Minimum) date (Access XP)

    Hans,

    Thanks! As usual, a perfect solution.

Posting Permissions

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