Select End of Month records

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

Hi,

I have a table with a date field and I wish to select the records which fall
on the last day of the month e.g 11/30/08, 12/31/08 etc. If the last day of
the month does not exist the the next day prior.

How do I go about this?

Bruce
 
Hi Bruce,

use Group and Last SQL commands.
e.g. this query works:

SELECT Last(tblDates.Start) AS LastOfStart, Month([start]) & "-" &
Year([start]) AS Month_Year
FROM tblDates
GROUP BY Month([start]) & "-" & Year([start]);

Stefan.
 
Instead of LAST use MAX. Max will give you the latest date in the
month, LAST might give you the latest date, but it might give you any
other date in the month. LAST is the LAST record accessed in the group
and since the order of the records is not known at the point that Last
is selecting the value the record selected can be more or less random.

SELECT Max(tblDates.Start) AS LastOfMonth, Month([start]) & "-" &
Year([start]) AS Month_Year
FROM tblDates
GROUP BY Month([start]) & "-" & Year([start]);


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Stefan_889_12 said:
Hi Bruce,

use Group and Last SQL commands.
e.g. this query works:

SELECT Last(tblDates.Start) AS LastOfStart, Month([start]) & "-" &
Year([start]) AS Month_Year
FROM tblDates
GROUP BY Month([start]) & "-" & Year([start]);

Stefan.

Bruce said:
Hi,

I have a table with a date field and I wish to select the records which fall
on the last day of the month e.g 11/30/08, 12/31/08 etc. If the last day of
the month does not exist the the next day prior.

How do I go about this?

Bruce
Click to expand...
Click to expand...
 
Back
Top