# Thread: End of Month in Query

1. Registered User
Join Date
Aug 2008
Posts
4

## Unanswered: End of Month in Query

Hi all...I need to convert an EOMONTH statement into an Access Query. The current formula looks like this:

=EOMONTH(B16,(-1))

B16 now = ProposedEffDate

Is there a function in Access that will do this for me?

Thanks

Kelly

2. Moderator
Join Date
Dec 2004
Location
Posts
3,928
Maybe this will help:

Also note: if you are using this in a query, you can use the call to a function as part of your expression in a query - ie...

EOMonth: LastOfMonth([MyDateField])

or you could simply put in the equation from the function as the expression in your query:

EOMonth: DateAdd("m", 1, DateSerial(Format([MyDateField],"y"), Format([MyDateField],"m"), 1)) - 1

So you can create functions and use the functions in the query or just use the equation from the function itself in your query.

Here are some simple date manipulation functions that can be used to return a specific date in the future/past.
Note that you can combine these functions to, for example, find the lastday of next month:
newdate = LastOfMonth( NextMonth( olddate ) )
'*************************** Code Start *******************************
'This code was originally written by Lewis Moseley.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Lewis Moseley
'
Function FirstOfMonth(InputDate As Date)
' Return a date that is the first day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer

If IsNull(InputDate) Then
FirstOfMonth = Null
Else
D = Day(InputDate)
M = Month(InputDate)
Y = Year(InputDate)
FirstOfMonth = DateSerial(Y, M, 1)
End If
End Function

Function LastOfMonth(InputDate As Date)
' Return a date that is the last day of the month of the date passed
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)
'find the first day of next month, then back up one day
LastOfMonth = DateAdd("m", 1, DateSerial(Y, M, 1)) - 1
End If
End Function

Function NextMonth(InputDate As Date)
' Return a date that is one month later than the date passed
End Function

Function LastMonth(InputDate As Date)
' Return a date that is one month before the date passed
End Function

Function SetDayOfMonth(InputDate As Date, DayToSet As Integer)
' Return a date that is the specified day of the month and year passed
Dim M As Integer, Y As Integer

If IsNull(InputDate) Then
SetDayOfMonth = Null
Else
M = Month(InputDate)
Y = Year(InputDate)
SetDayOfMonth = DateSerial(Y, M, DayToSet)
End If
End Function
'*************************** Code End *******************************
Last edited by pkstormy; 08-18-08 at 13:44.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
well you could look for dates that are less than the 1st of the next month

where mydatecolumn< #01 Sep 2008#

you can build your date selector using a calendar control, or a quick and dirty bit of VBA code called as a function

Code:
```function StartofNextMonth (optional datevalue as date = date()) as text
'this function returns the date of the Start of next month
if a date value isn't supplied then reutn the start the next month based on todays date
if month(datevalue=12) then
StartofNextMonth = (cdate("#01/01/" & year(datevalue)+1)& "#")
else
StartofNextMonth = (cdate("#" & month(datevalue)+1 & "/01/" & year(datevalue))& "#")
endif
end function```
note this is entirely air code... its not been tested.. im not sure even if it is exactly correct..

HTH

4. Registered User
Join Date
Aug 2008
Posts
4
Thanks for the replies. To be more specific, here is what I'm looking for. I'm kinda new at this stuff, so please bear with me.

I have a column in my table called "Proposed Effective Date". In the query, I need to have a formula that will go back one month from that date and return the last day of that month.

I can get it to go back one month with this:

But I can't figure out how to fix it to return the end of that month.

5. Moderator
Join Date
Dec 2004
Location
Posts
3,928
You may not have seen this part I added to my post...

or you could simply put in the equation from the function as the expression in your query:

EOMonth: DateAdd("m", 1, DateSerial(Format([MyDateField],"y"), Format([MyDateField],"m"), 1)) - 1

So you can create functions and use the functions in the query or just use the equation from the function itself in your query.

To return the end of the month from the previous month, just adjust the equation to go back 1 month.

Maybe this (untested):

EOMonth: DateAdd("m", 1, DateSerial(Format([MyDateField],"y"), Format([MyDateField],"m"), 1)) - 2
or
EOMonth: DateAdd("m", 2, DateSerial(Format([MyDateField],"y"), Format([MyDateField],"m"), 1)) - 1
or
EOMonth: DateAdd("m", 1, DateSerial(Format([MyDateField],"y"), Format([MyDateField],"m"), 2)) - 1
or
EOMonth: DateAdd("m", -1, DateSerial(Format([MyDateField],"y"), Format([MyDateField],"m"), 1)) - 1

Not sure but experiment as one of the above answers might work (or you may have to put in a -1 instead of a 1)....again, I don't have MSAccess readily available right now to test it (I think the last one might work for you though.)
Last edited by pkstormy; 08-18-08 at 13:54.

6. Registered User
Join Date
Aug 2008
Posts
4
This is what I put in the query:

Proposed Effective date: = 10/1/2008

What I get is : 8/31/275

????

Thanks for all the help!

7. Moderator
Join Date
Dec 2004
Location
Posts
3,928
You may need to do this (again, I don't have MSAccess open so I'm kind of guessing):

or
or

I think it's just a matter of using yyyy versus y or mm versus m (or both) but again, experiment.

8. Registered User
Join Date
Aug 2008
Posts
4
Nevermind...I finally got it to work! Here is what I used: