Using MAX is not pulling the maximum value when seperated by seconds

[S Davis]

Hello all,

I am trynig to find the maximum value based off of dates that contain
times down to the second. There are a few that are only seconds apart
on the same day, and even my query containing MAX returns the both of
them.

Is there any way to get Access to recognize that they are not the same
value and only return the latter one?

PS - Using LAST also has the same problem.

MRC LastOfEVT_DATE EVT_OBJECT BusMRC_History_MonthF
178 9/19/2006 1:30:56 PM 7279 1
178 2/23/2007 8:44:12 AM 7279 6
176 2/23/2007 8:44:34 AM 7279 6
179 3/27/2007 1:38:34 PM 7279 7
178 4/11/2007 1:53:49 PM 7279 8

 

[Michael Gramelspacher]

Hello all,

I am trynig to find the maximum value based off of dates that contain
times down to the second. There are a few that are only seconds apart
on the same day, and even my query containing MAX returns the both of
them.

Is there any way to get Access to recognize that they are not the same
value and only return the latter one?

PS - Using LAST also has the same problem.

MRC LastOfEVT_DATE EVT_OBJECT BusMRC_History_MonthF
178 9/19/2006 1:30:56 PM 7279 1
178 2/23/2007 8:44:12 AM 7279 6
176 2/23/2007 8:44:34 AM 7279 6
179 3/27/2007 1:38:34 PM 7279 7
178 4/11/2007 1:53:49 PM 7279 8

perhaps try this, but use your real table name:

SELECT table3.*
FROM table3
WHERE lastofevt_date = (SELECT MAX(a.lastofevt_date)
FROM table3 AS a
WHERE DATEDIFF("m",0,a.lastofevt_date) = DATEDIFF
("m",0,table3.lastofevt_date)
GROUP BY DATEDIFF("m",0,a.lastofevt_date));

 

[John W. Vinson]

Hello all,

I am trynig to find the maximum value based off of dates that contain
times down to the second. There are a few that are only seconds apart
on the same day, and even my query containing MAX returns the both of
them.

It shouldn't. Please post the SQL of your query.

John W. Vinson [MVP]