have calculated date field show end of month date 1

[aug4570]

I have a calculated date field in a query that will calculate 4 yrs from a begin date. I want the result to show an end of month date (either 30, 31 or 28 depending on the month.) Is it possible? Please keep it as simple as possible as I'm not familiar w/VB. If it'll be complicated, is there anyway I can just use a standard mask for the calculated result to display as "mm/31/yy" in this query? Thanks in advance.

 

[MichaelRed]

Aug44570,

The below will, assuming you have the proper declarations, provide the "Last day" of the Next month for the 48 Months (Starting with the current Month). Of course, you will need to replace the debug.print statement w/ some meaningful use for the calculated variables.

MyDate = Format(Trim(Str(Month(Now()) + 1)) & "/1/" & Trim(Str(Year(Now()))), "Short Date&quot

For Idx = 0 to 48
MyDate = DateAdd("m", Idx, MyDate)
Eom = DateValue(MyDate) - 1)
Debug.Print MyDAte, EOM
Next Idx


MichaelRed
There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Aug4570,

Sorry, should have read my own tag line. The dateadd function needs to use "1", NOT "Idx" - Should be:

For Idx = 0 to 48
MyDate = DateAdd("m", 1, MyDate)
Eom = DateValue(MyDate) - 1)
Debug.Print MyDAte, EOM
Next Idx


MichaelRed
TRhere is never time to do it right but there is always time to do it over