End of Month dates 5

[jdttek]

Hi
I download loan balances (Acct #, Amounts and Dates) each day into an Access table. I then run an append query that adds each account balance to a cumulative table so that I have a running history of balances each day.

What I need to do now is just extract month end balances from the trended table. Excel has an EOMonth function that calcs end of month. IS there anything equivilent in Access or with code that will allow me to do this? Thanks.

JDTTEK

 

[BrainB]

I use ThisMonth = Month(Now()). Perhaps you could use this to compare with the Month() of your date (returns numeric 1 to 12).


Regards
BrianB
** Let us know if you get something that works !
================================

 

[glenntb]

Something like this should work:

EOM = (DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)

Essentially, adding one month to today's date, forcing the day = 1, then subtracting 1 day to get the end of the current month.

Hope this helps!

-Glenn

 

[almostfrugal]

Or even simpler:

EOM=Date(Year(Now()),Month(Now())+1,0)

I discovered this by accident. The zero-th day of a month is the last day of the previous month. Negative numbers go further back in the previous month.

Harvey

 

[almostfrugal]

I need to elaborate on my last post.

To get the last day of the current month, you can use a formula in an Excel spreadsheet such as:

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)

or in VB code you could use:

EOM=DateSerial(Year(Now),Month(Now)+1,0)

Again, the 0 in each example gives the last day of the preceding month. Negative numbers would go further back in the previous month.

Harvey

 

[tatarrila]

I think better way is the function I use (that I learn in tek-tips forum - Thanks to every body)

Function LastOfMonth(InputDate As Date)
Dim d As Integer, m As Integer, y As Integer
If IsNull(InputDate) Then
LastOfMonth = Null
Else
d = Day(InputDate)
m = Month(InputDate)
y = Year(InputDate)
LastOfMonth = DateAdd("m", 1, DateSerial(y, m, 1)) - 1
End If
End Function

antonio

 

[glenntb]

Very cool Harvey! Most worthy of a star! I will remember that one!

-Glenn

 

[Cresby]

Yea I did the month+1 and subtract 1 (day) from the numeric value wheeze. I needed it to get Excel to work in '97 on Win 95 and needed the last day of the month for events that occur on the last (say) Saturday of every month. Mind you the year rollover has to be accounted for in Dec even though few events try to compete with Xmas (or Hunnika)!!!!

 

[jdttek]

I cast my vote to Harvey w/ a Star. Thanks. Nice idea and works like a charm.

JDTTEK