Find max date value in multiple columns

[Eric.Haflett]

I found this query that claims it can display the maximum (latest) date
in multiple columns containing dates. I have a table that contains
inspection dates for multiple reasons, each reason is a column with the
a date or null. I need a query that will tell me the last time the
item was inspected. Here's what I have so far...

SELECT ITEMNAME, (select max(dateValue)
from (select LiftEnd as dateValue
union all
select ManEnd
union all
select LiftQA
union all
select ManQA) as dates) AS MaxDateValue
FROM tblITEMS;

Can this query really do what I need? It seems to but I can't get it
to work. I'm working in MS Access 2000. Can anyone suggest a
fix/alternative?

Thank you!

 

[Duane Hookom]

You should normalize your table structure. We don't have a clue what your
table structure is other than guessing. You could possible normalize your
table with a proper union query like:

SELECT DateFieldA as InspDate, "A" as InspType
FROM tblUnNormalized
WHERE DateFieldA is not Null
UNION ALL
SELECT DateFieldB, "B"
FROM tblUnNormalized
WHERE DateFieldB is not Null
UNION ALL
SELECT DateFieldC, "C"
FROM tblUnNormalized
WHERE DateFieldC is not Null
----
UNION ALL
SELECT DateFieldX, "X"
FROM tblUnNormalized
WHERE DateFieldX is not Null;

You can then find the maximum date by querying the union query.

 

[Dale Fye]

Eric,

I have a function I use whenever I have to deal with denormalized data like
yours. Insert this function in a code module, then call it in your query.
You can pass it as many values (actually I think there is probably a limit
to the number of parameters you can pass a function, but I'm not sure if
that applies to arrays) as you need to and it will return the maximum value.
Since the array is a variant, this will work with numeric values as well as
dates. I also have another function Minimum() that does the opposite of
this one.

Public Function Maximum(ParamArray MyArray()) As Variant

Dim intLoop As Long

Maximum = Null
For intLoop = LBound(MyArray) To UBound(MyArray)

If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(Maximum) Then
Maximum = MyArray(intLoop)
ElseIf MyArray(intLoop) > Maximum Then
Maximum = MyArray(intLoop)
End If

Next

End Function

To use this in your query, do something like:

SELECT NAME, Maximum([LiftEnd], [ManEnd], [LiftQA]) as MaxDate
FROM tblItems

HTH
Dale