I would like to find the end of month dates for each month between a given date range. For instance if the user of my database entered a start date of 01/01/10 and an end of 12/31/10, the result would display 01/31/10, 02/28/10, 03/31/10, etc... until the end of month date is equal to then end dateof 12/31/10.

Thanks for the help.

Matt wrote:
This is the perfect application for a calendar table. Think how easy this
would be if you had a table cotaining columns for CalDate, CalMonth and
CalYear, with rows for each date in a year:

SELECT Max(CalDate)
FROM CalendarDates
WHERE CalDate >= [Enter Start Date]
AND CalDate <= [Enter End Date]
GROUP BY CalYear, CalMonth

If you had a Numbers table (a table containing a single column called
fNumber) containing rows of numbers incrementing from 1 to, say, 1000 (you
could use some simple VBA code to populate it), you would be able to use a
query to populate the calendar table:

INSERT INTO CalendarDates (CalDate,CalYear,CalMonth)
SELECT #12/31/2009# + fNumber,
Year(#12/31/2009# + fNumber),
Month(#12/31/2009# + fNumber)
FROM Numbers

Without a Numbers table, you would need some VBA code to populate the dates.

This would not be the only use for a calendar table. Think about adding
columns to designate holidays, weekends, workdays, fiscal periods if
applicable, etc.

On Aug 17, 12:45*pm, Matt <mfied... (AT) 1fbusa (DOT) com> wrote:
Copy paste this in a code module and run from the immediate window.
Maybe it will help.

Sub Next12()
Dim i As Integer
Dim d As Date
d = #1/15/2011#
For i = 1 To 12
d = DateAdd("m", 1, d - Day(d) + 1) - 1
Debug.Print d
d = DateAdd("m", 1, d)
Next
'MsgBox "Done "
End Sub

On Aug 19, 8:09*am, Patrick Finucane <patrickfinucan... (AT) gmail (DOT) com>
wrote:
Thanks. This got me started in the right direction. Here is what I
actually came up with by combining your code with code for finding the
number of work days between two dates.

Function MONTH_END_DATE(BEGDATE As Variant, ENDDATE As Variant) As
Date

Dim i As Integer
Dim EOM_DATE As Date

BEGDATE = DateValue(BEGDATE)
ENDDATE = DateValue(ENDDATE)

Do While BEGDATE <= ENDDATE
BEGDATE = DateAdd("m", 1, BEGDATE - Day(BEGDATE) + 1) - 1
EOM_DATE = BEGDATE
'Debug.Print EOM_DATE
BEGDATE = DateAdd("m", 1, BEGDATE)
Loop

End Function

Can you tell me how to get the listing of the dates to come back in
the results of a query?

Thanks again.

Have you considered using a dateandtime table? This is common
practice in business intelligence designs. You have a table with each
day/date/year, day of the week, workday, weekend, first of month,
last of month, holiday, and so on. It's very handy for structuring
data. If you add a unique-id as well it becomes very easy to use in
JOIN statements.

Just a thought.

--
Cheers

The Frog

The Frog wrote: