Dear All,
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
WHERE ((([Assessment Tracker].Candidate) Like "*" & [Candidate Name] & "*"))
GROUP BY [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date]
ORDER BY [Assessment Tracker].Candidate, Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) DESC;
Output:
Candidate Unit EV1 Date EV2 Date Achdate
TH1 10 07/05/2015 25/05/2015 25/05/2015
TH1 10 07/05/2015 07/06/2015 07/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I
' Return the maximum value found.
Maxdate = currentVal
End Function
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 10 25/05/2015
I expect it to return
Candidate Unit MaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date.
I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
Any thoughts and suggestions are welcome.
Regards
Tony (5 days into Access.....)
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
WHERE ((([Assessment Tracker].Candidate) Like "*" & [Candidate Name] & "*"))
GROUP BY [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date]
ORDER BY [Assessment Tracker].Candidate, Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) DESC;
Output:
Candidate Unit EV1 Date EV2 Date Achdate
TH1 10 07/05/2015 25/05/2015 25/05/2015
TH1 10 07/05/2015 07/06/2015 07/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I
' Return the maximum value found.
Maxdate = currentVal
End Function
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 10 25/05/2015
I expect it to return
Candidate Unit MaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date.
I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
Any thoughts and suggestions are welcome.
Regards
Tony (5 days into Access.....)