VBA Formula for: Last Day of Last Month

[ShredDude]

Hi!

I'm used to Excel, and trying to convert some things I've done there to Access. (Without using a Reference to the Excel Object Library in my Access Project)

I'm looking for the VBA equivalent of this Excel formula:

=EOMONTH(TODAY(),-1)

Which gives me the SerialDate # of the last day of last month.

Maybe a combo of VBA's Month function and DateAdd and Dateserial? I could envision a Case Select structure with 3 Cases; one for the months with 30 days, one for the months with 31 days, and one for February. Then you could just decrement Today's Month by one (unless it is 1 then it becomes 12, and you decrement the Year by 1), then use the case select for the appropriate Day value of the decremented Month. Sounds like the makings of a Custom Function for my project.

Is there something more concise within VBA already?

Looking for a quick tip so I don't reinvent the wheel. Excel's Worksheet Function EOMONTH is nice because it inherently knows how many days each month has.

Thanks in advance...

Shred

 

[Banana]

Actually, I'd do something like this:

LastDayOfMonth = DateSerial(Year(Date()), Month(Date()+1) -1)

Which will always give you the last day of the current month. You can then make it a function:

Public Function EOMonth(dInput As Date)

LastDayOfMonth = DateSerial(Year(dInput()), Month(dInput()+1), -1)

End Function

Then you can call it with this:

EOMonth Date()

HTH.

 

[pbaldy]

In case you need others, there are a lot here:

http://www.pacificdb.com.au/Support/code_datetime.htm

 

[ShredDude]

I see where you're going with that idea, but the code you posted doesn't quite do it.

The DAY parameter is not optional in the DATESERIAL function. You'd still need to account for a change to the YEAR parameter if DATE() is within January, and in order to be accurate, you need to know how many days the previous month has (31,30 or 28).

That's why I keep getting to the idea of a custom function. I'm hoping there's a more elegant solution out there though.

Thanks for the reply.

 

[Banana]

Did you try it?

?#1/1/2009#-1
12/31/2008

EDIT: Noted I forgot a comma:

LastDayOfMonth = DateSerial(Year(dInput()), Month(dInput()+1)[color=red],[/color] -1)

To clarify- I had thought you wanted the last day of current month, which is what the above code does. If you want the last day of previous month, remove the +1 from the Month portion.

 

[ShredDude]

Banana:

I modified yours slightly to get this:

DateSerial(Year(Date()), Month(Date()+1), 0)

Now it returns the result I'm seeking. It will always return the last day of last month.

Thanks for the pointer in the right direction.

 

[Banana]

Ooo, forgot about that weird one-off thingy with Date. Glad you worked it out yourself.

 

[ShredDude]

It actually needs to be like this:

DateSerial(Year(Date()), Month(Date()), 0)

That way, if it's the last day of the month eg. jan 31 2009, it will still return the teh day before the first day of that month.

From the Help file on DateSerial:

The following example uses numeric expressions instead of absolute date numbers. Here the DateSerial function returns a date that is the day before the first day (

1 - 1

Behavior can be verified quickly in the Immediate window with something like..

?DateSerial(2009, 4, 0)

Which will return 3/31/2009. The day before the first day of April, 2009.

Thanks for the dialog on this. It's always good to learn something new!