Get latest date from 4 columns function

Question

I have the following query "qryE".

Equipment   E1_45J          E2_45J         E3_45J      E4_45J
400         2019-03-25      2019-10-07  
401         2019-04-01      2019-10-23  
402         2019-02-14      2019-08-25                 2019-11-11
403         2019-02-11      2019-09-05     2019-11-25

I made a function to return the latest value between these 4 columns:

Function getmaxdate(dteDate1 As Date, dteDate2 As Date, dteDate3 As Date, dteDate4 As Date) As Date

    Dim dteMaxDate As Date

    dteMaxDate = dteDate1
    Debug.Print dteDate2
    If IsDate(dteDate2) Then
        If dteDate2 > dteMaxDate Then dteMaxDate = dteDate2
    If IsDate(dteDate3) Then
        If dteDate3 > dteMaxDate Then dteMaxDate = dteDate3
    If IsDate(dteDate4) Then
        If dteDate4 > dteMaxDate Then dteMaxDate = dteDate4
    getmaxdate = dteMaxDate

End Function

What I'm trying to achieve is this:

 Equipment   45J          
 400         2019-10-07  
 401         2019-10-23  
 402         2019-11-11
 403         2019-11-25

However, when I run the query with 45J:

getmaxdate([E1_45J],[E2_45J],[E3_45J],[E4_45J])

I'm getting Data type mismatch in criteria expression.

I tried to force the format of the values of my query with:

45J: getmaxdate(Format([E1_45J], "yyyy/mm/dd"),Format([E2_45J], "yyyy/mm/dd"),Format([E3_45J], "yyyy/mm/dd"),Format([E4_45J], "yyyy/mm/dd"))

However, I'm still getting the same error.

What am I doing wrong?

There are blank values, access is a stickler for data types, and null dates are problematic in my experience. You will need to check each for blanks and then skip it. — Warcupine, Dec 19 '19 at 16:52
Ideally you want one column for date, but if you can't change the table design, you could use a union all to unpivot the data and use then use max function in the outer query. https://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access — Radagast, Dec 19 '19 at 16:55
In addition to above suggest SQL answer - for a potential pure VBA solution - http://allenbrowne.com/func-09.html — Mike, Dec 19 '19 at 17:02

score 1 · Accepted Answer · answered Dec 19 '19 at 17:11

Presumably you're getting a type mismatch because you're passing a Null to a Date parameter, and you can't do that.

Change the function's signature to accept Variant values instead of Date - a Variant can hold a Null value.

With all parameters defined As Date, the IsDate function can only ever return True.

I'd suggest not assuming date1 is a valid date, and default the "max date" to CDate(0): if the record has any dates in it, it'll be greater than that.

Careful to close your If...End If blocks, too.

score 1 · Answer 2 · answered Dec 19 '19 at 17:13

You can do this using SQL on qryE, my table is tblTesting, so you would change that.

select equipment,MAX(d) as mxDate from
(
SELECT 
equipment,
E1_45J as d from tblTesting 
UNION
SELECT 
equipment,
E2_45J as d  from tblTesting 
UNION
SELECT 
equipment,
E3_45J as d  from tblTesting
UNION
SELECT 
equipment,
E4_45J as d  from tblTesting
)
group by equipment

Get latest date from 4 columns function

2 Answers2