Select End of Month records

[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

 

[Stefan_889_12]

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.

 

[John Spencer]

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
'====================================================

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.

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