Combining 3 date fields to create a new variable 1

[leeroi]

I have three date fields: date1, date2, and date3. I would like to create a new variable, MinDate, for each record which is the minimum (earliest)of these three dates. MinDate would then be used in a Datediff computation with a fourth date variable.

How do I do this?

Lee

 

[josephwalter]

I'm not clear on the context of what you're working on, but I thought this may help:

Public Function getMinDate(date1 As Date, date2 As Date, date3 As Date)
    Dim tmp As Date
    If date1 < date2 Then
        If date1 < date3 Then
            tmp = date1
        Else
            tmp = date3
        End If
    Else
        If date2 < date3 Then
            tmp = date2
        Else
            tmp = date3
        End If
    End If
    getMinDate = tmp
End Function

 

[MichaelRed]

Less (logic) is More (processing)? At least this is more generic, as you may pass any number of any type of args and not get an error (although I haven't tried it w/ mixed mode args)

Public Function basMin(MyArray As Variant) As Variant

    'Michael Red 1/25/2001  To find the Mininum Value of a Set

    Dim Idx As Long
    Dim MinVal As Double

    MinVal = MyArray(0)

    For Idx = 0 To UBound(MyArray) - 1
        If (MyArray(Idx + 1) < MyArray(Idx)) Then
            MinVal = MyArray(Idx + 1)
        End If
    Next Idx

    basMin = MinVal

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[josephwalter]

Very excellent code, MichaelRed.

 

[MichaelRed]

Thank you
[tab]Thank you
[tab][tab]Thank you

Kudos
[tab]Gold stars and
[tab][tab]employment opportunities

equally (and whole heartedly) welcome

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[leeroi]

I have to admit that, barely beyond the neophyte stage in mastering VBA, I did not uderstand Michael's UDF. I was however, able to call Joseph's UDF in a query to get the results I needed. Only problem is that for any particular record there may be a null value for Date1, Date2, or Date3. Joseph's function only works where all three fields have data in them. How do I deal with null values, and still get a min-date if only one or two Date fields have values in them?

Lee

 

[MichaelRed]

Go (back) to my version. It was designed w/ that possability as a part of the design / implementation. With the "Fixed" number of fields -which may include NULL- you will get increasingly more difficult additions to the code and / or the calling arguments.

As you note you are " ... barely beyond the neophyte stage in mastering VBA ... ", so the argument syntax for the procedure I posted may not be obvious to you. I'm sure that your knowledge would be advanced by going through the help system and finding examples of using ParamArray in the calling sequence of a procedure. Expecting that you (mistakenly) think that a better/quicker answer is forthcomming through posting, I will stte that (for YOUR narrow use) you just pass the procedure the (three) fields and assign the results as you desire.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[josephwalter]

After adding ParamArray to the call, I tried passing the function a Null value for the 2nd of 3 parameters:

basMin(#09/01/02#, Null, #07/01/02#)

The function returned the #09/01/02# value. How would you adjust the code to handle this kind of situation, Red?

 

[leeroi]

OK. I've pasted Michael's function as is into an Access module. I then set up a query with the following sql code:

SELECT Referrals.EvalDate, Referrals.Cancel1, Referrals.NoShowDate1, basmin([evaldate],[cancel1],[noshowdate1]) AS mindate
FROM Referrals;

When I run the query, it tells me I have the wrong number of arguments used with the function. I seriously don't know what I'm doin' here! What needs to be changed?

Lee

 

[josephwalter]

The first line should be changed to this:

Public Function basMin(ParamArray MyArray() As Variant) As Variant

As Red suggested, read the help files on ParamArray.

Note that the function returns a Double data type, not a date. You'll likely want to convert that to a date as below:

SELECT Referrals.EvalDate, Referrals.Cancel1, Referrals.NoShowDate1, CDate(basmin([evaldate],[cancel1],[noshowdate1])) AS mindate
FROM Referrals;

 

[MichaelRed]

Either asleep or administering Dr. Lerry's advice? anyway, posted the wrong one:

Public Function basMinVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MINIMUM or a series of values

    'Sample Usage:
    '? basMinVal(1, 5, 9, 3, 13.663)
    '1

    '?basMinVal(9, 1, 5, 3, 13.663)
    '1

    Dim Idx As Integer
    Dim MyMin As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
     Else
        MyMin = varMyVals(0)
    End If

    For Idx = 0 To UBound(varMyVals())
        If (varMyVals(Idx) < MyMin) Then
            MyMin = varMyVals(Idx)
        End If
    Next Idx

    basMinVal = MyMin

End Function
[code]

with following 'results':

? basMinval(#09/01/02#, Null, #07/01/02#)
7/1/02 
 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[leeroi]

Works beautifully. Thanks!