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

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
NextMonth = DateAdd("m", 1, InputDate)
End Function

Function LastMonth(InputDate As Date)
' Return a date that is one month before the date passed
LastMonth = DateAdd("m", -1, InputDate)
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 *******************************

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

note this is entirely air code... its not been tested.. im not sure even if it is exactly correct..

HTH

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:

ADPPerEnd: DateAdd("m",-1,[proposed effective date])

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

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.)

This is what I put in the query:

ADPPerEnd1: DateAdd("m",-1,DateSerial(Format([proposed effective date],"y"),Format([proposed effective date],"m"),1))-1

Proposed Effective date: = 10/1/2008
ADPPerEnd1 should = 9/31/2008

What I get is : 8/31/275

????

Thanks for all the help!

You may need to do this (again, I don't have MSAccess open so I'm kind of guessing):

ADPPerEnd1: DateAdd("m",-1,DateSerial(Format([proposed effective date],"yyyy"),Format([proposed effective date],"m"),1))-1
or
ADPPerEnd1: DateAdd("m",-1,DateSerial(Format([proposed effective date],"yyyy"),Format([proposed effective date],"mm"),1))-1
or
ADPPerEnd1: DateAdd("mm",-1,DateSerial(Format([proposed effective date],"yyyy"),Format([proposed effective date],"m"),1))-1

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

Nevermind...I finally got it to work! Here is what I used:

ADPPerEnd1: DateAdd("m",0,DateSerial(Year([proposed effective date]),Month([proposed effective date]),1))-1

Thanks again for all of your help!