CODEWITHSUNDEEP
vbams-access
michaelnoze08
michaelnoze08

Reputation: 49

Get latest date from 4 columns function

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?

Upvotes: 2

Views: 76

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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.

Upvotes: 1

Related Questions