first of the month date question

A

ave

I have a table that contains the date/time for each record

How would i write a query so i could show the records for the first day of
each month

Likewise to show the last day of each month

Thanks in advance
 
D

Douglas J. Steele

First day of the current month is DateSerial(Year(Date), Month(Date), 1).
Last day of the current month is DateSerial(Year(Date), Month(Date) + 1, 0)

If you've got an arbitrary data that you want to use instead of the current
date, substitute your field for Date in the function calls above.
 
J

John Spencer (MVP)

Hmm, I interpreted your question differently. That is you wanted to get all
records for the first and/or last day of a number of months.

First Day of each month for all the months in the database

That would be
WHERE Day(SomeDateField) = 1

Last Day of Each Month is a bit trickier- Add one to the date and see if it is
now the first of the (succeeding) month.

WHERE Day(DateAdd("d",1,SomeDateField)) = 1


I hope that between Mr. Steele and I that you have the answer you wanted.
 
Top