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

  • Thread starter Thread starter S Davis
  • Start date Start date
S

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
 
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
Click to expand...
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));
 
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.
Click to expand...

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

John W. Vinson [MVP]
 
Back
Top