Dmax with dates less than 2000

[Countymnca]

Hello,
I have a db that tracks employee movement (promotion, demotion, transfer, resign, rehire, etc) within a large organization. Each movement is recorded by the employee number, date, type of movement, and new position number. Initally I created a query that is supposed to pull the Last employee movement date, but noticed it didnt work properly in some case. I changed it to pull the max date and that fixed alot of incorrect pulls.

I know notice that if the employee's last movement date was <1/1/2000 that it pulls the lowest date instead of the max. Last and Max both do this.

For example,
If the last employee movement date was 6/5/99 when they were promoted, and they had a hire date of 1/5/97, it will show 1/5/97 in the query. If I enter in a new movement for them that falls in 2000 or later (2/5/2001) and rerun the query, it pulls 2/5/01.

What is the difference between Max and Last and why does dates <2000 affect the results?

Thanks in advance....

 

[PHV]

Last is unreliable, period.
What is the data type of the date field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Countymnca]

Date/Time that captures mm/dd/yyyy

The input mask on the data entry for the date field is mm/dd/yy.

 

[PHV]

So, what is your current SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Countymnca]

SELECT tHistory.EmpID, Max(tHistory.SDate) AS LastOfSDate
FROM tHistory
GROUP BY tHistory.EmpID
HAVING (((tHistory.EmpID) Is Not Null))
ORDER BY tHistory.EmpID, Max(tHistory.SDate) DESC;

I left it AS LastOfDate so I didnt have to correct all the other querys that use this.

 

[lespaul]

maybe it's the order by that's making it screwy, try this:

SELECT tHistory.EmpID, Max(tHistory.SDate) AS LastOfSDate
FROM tHistory
WHERE (((tHistory.EmpID) Is Not Null))
GROUP BY tHistory.EmpID
ORDER BY 1, 2 DESC;

Leslie

Have you met Hardy Heron?

 

[Countymnca]

No apparant change, however the records that I found prior to these were already corrected by deleting them and then reentering the information directly in the table. I just started with this system and dont know if that data was imported from somewhere or entered via the db. Would that matter? I cant figure out any other reason.