Find the last date in a month 1

[GLackey]

I have a date field in a table that I need to find the last day of that month for. Suggestions? I'm doing this inside a query.

 

[SteveR77]

Here is what you will need to add to a Module -

******************************************************
'Declarations Section of Module
'******************************************************
Option Explicit

'******************************************************
'FindEOM Function
'******************************************************
'This function takes a date as an argument and returns the last
'day of the month.
Function FindEOM (MyDate)

   Dim NextMonth, EndOfMonth
   NextMonth = DateAdd("m", 1, MyDate)
   EndOfMonth = NextMonth - DatePart("d", NextMonth)
   FindEOM = EndOfMonth

End Function

In the query you will need your Date field and another
field in the query that looks like this -


EndOfMonth: FindEOM([TESTDATE])

 

[GLackey]

Thanks, that's a more elegant way of doing it than what I've gotten so far.

Appreciate the assist.

 

[GoDawgs]

You can also just keep that same formula (basically) in the query instead of creating a module:

DateAdd("m",1,[DateField])-DatePart("d",[DateField])

Just another idea...I like to keep it out of the modules, but there are definitely plenty of reasons to create a function as well. Hope that helps.

Kevin

 

[SteveR77]

Kevin,

Good point on having the function in the query. I have written DBs both ways.

The upside of the module is not having to embed the code multiple times if there is more than one query using the same code, and it gives you a single point to maintain the code as well, this is nice if you have a large number of queries in your DB.

 

[MichaelRed]

Please enlighten me. Why " ... keep it out of the modules ..."?





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GoDawgs]

If I'm creating one report that uses the end of month, I don't need to keep a function around for it. I also know that there are a lot of people looking for answers on this site that are somewhat afraid of vba code...so I posted another way to do the same thing without using code. Steve's response was perfect, I just threw out another option in case someone is confused.

I do love that I have to defend myself for trying to help someone else out though.

 

[CajunCenturion]

I too GoDawqs love the fact that from within the forum, you get the opportunity to look at multiple approaches, and those who presented them are willing to discuss the pros and cons of them so that you can properly evaluate each alternative and thus make an informed and intelligent choose as to which is better for your individual situation.

I think it's one of strengths of these fora.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PaulBricker]

Another way to do this in a query is use the DateSerial function.
DateSerial(Year(Date()),Month(Date())+1,0)
This will return 12/31/03. If you have a date field you are running this against, just replace Date() with the name of your date field.

Paul

 

[SteveR77]

The forums are TIMTOWTDI!

Oh, TIMTOWDI is { There is more than one way to do it )
and the forums have helped me answer a wide array of questions in the past, so I only hope that my post can help someone else.

Steve

 

[PaulBricker]

I think another reason to keep it out of the modules is that after A97, when you open one module in Access you open ALL modules. This is a memory drain for larger code heavy apps.

Paul