MAX(Completion_Date)

[davidg2356]

Hello,
I have read the other postings, and I am still having problems. I have a
query that is supposed to give me the most recent (or last Completion_Date)
of either Training or Education. My first query is a union to get all the
Completion_Dates into one column, and my second query (below) is supposed to
pull up the most recent completion date per employee. (The basis for all of
this is to ultimately pull up every course between the last course taken and
the previous 2 years.)


In the first query one employee has two completion dates one being 7/30/2003
and the other 7/15/2004. For some reason, it returns 7/30/2003 as being
greater than 7/15/2004. In other word the MAX(Completion_Date) is 7/30/2003,
which is clearly wrong.

Sorry, I do not know VB, but I appreciate your time.

THANKS,
David

SELECT EMPLOYEE.Emp_ID, MAX(DATE_COURSES.DATE_FIN) AS LAST_COURSE
FROM DATE_COURSES INNER JOIN EMPLOYEE ON DATE_COURSES.Emp_ID = EMPLOYEE.Emp_ID
GROUP BY EMPLOYEE.Emp_ID
UNION SELECT EMPLOYEE.Emp_ID, NZ(MAX(DATE_COURSES.DATE_FIN),0)
FROM DATE_COURSES RIGHT JOIN EMPLOYEE ON DATE_COURSES.Emp_ID= EMPLOYEE.Emp_ID
WHERE EMPLOYEE.Emp_ID<>ALL (SELECT DATE_COURSES.Emp_ID FROM DATE_COURSES)
GROUP BY EMPLOYEE.Emp_ID;

 

[Roger Carlson]

Is Completion_Date really a date? IOW, is it a Date/Time field? If it is a
Text field, then they are being sorted correctly.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[davidg2356]

Yes, I have it as a Date/Time data type. I do, however, have its format as a
Short Date as well as the Input Mask. Does that matter?

 

[Roger Carlson]

If you use the Format Property of the table, it should not matter. If you
use the Format() function in the query, then it is being converted to text.
However, it does not look like this is what you are doing. It's a mystery
to me. Sorry.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer (MVP)]

The Use of the NZ is forcing the date to a text string. Try surrounding the
expressions with CDate to force the string to a date. Also, you may need to
rearrange the order of the function calls.

First force null to value, then convert it to a date, then get the max.

.... MAX(CDate(NZ(DATE_COURSES.DATE_FIN,0))) ...

Although,
CDate(NZ(Max(Date_courses.Date_FIN),0))
Should also work

 

[davidg2356]

Thank you that works great! It is much more efficient, than what I was
doing.
(I had used DatePart to split the nz date into year, month, day, then I
concatenated them together and used the MAX function).
Thanks again,
David