Query to combine data columns

[jln via AccessMonster.com]

IM stumped What i have is 9 colums that have different dates . I need to
query for the newest date and only the newest and place them into one column.
HOW ?

 

[John Spencer]

I would think that you will need a custom function to do this.

Field: TheMaxDate: MaxVal(DateField1, DateField2, DateField3,...,dateField9)

Dale Fye posted this a while back, Copy it into a module and save it. and
then use it in your query.

'====== Code follows ======
Public Function MaxVal(ParamArray MyArray()) As Variant

Dim varMax As Variant
Dim intLoop As Integer
varMax = Null

For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(varMax) Or MyArray(intLoop) > varMax Then
varMax = MyArray(intLoop)
End If
Next
MaxVal = varMax

End Function
'====== END Code ======

 

[Duane Hookom]

I would normalize the date columns with a union query. You could then create
a totals query that displays the Max of the dates for a particular ID.

 

[jln via AccessMonster.com]

I have never done a union query how would i do it?

 

[Duane Hookom]

You would need to create a new query and go to the SQL view. Then enter
something like:

SELECT IDField, DateFld1 as TheDate, "Date 1" as DateTItle
FROM tblTooManyDates
UNION ALL
SELECT IDField, DateFld2, "Date 2"
FROM tblTooManyDates
UNION ALL
SELECT IDField, DateFld3, "Date 3"
FROM tblTooManyDates
UNION ALL
-- etc --
SELECT IDField, DateFld9, "Date 9"
FROM tblTooManyDates;