If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > End of Month in Query

 LSGKelly Registered User Join Date: Aug 2008 Posts: 4
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
 pkstormy Moderator Join Date: Dec 2004 Location: Madison, WI Posts: 3,925
 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 ******************************* __________________ Expert Database Programming MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 08-18-08 at 13:44.
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,302
 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 __________________ Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem
 LSGKelly 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: ADPPerEnd: DateAdd("m",-1,[proposed effective date]) But I can't figure out how to fix it to return the end of that month.
 pkstormy Moderator Join Date: Dec 2004 Location: Madison, WI Posts: 3,925

Last edited by pkstormy; 08-18-08 at 13:54.
 LSGKelly Registered User Join Date: Aug 2008 Posts: 4
 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!
 pkstormy Moderator Join Date: Dec 2004 Location: Madison, WI Posts: 3,925
 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. __________________ Expert Database Programming MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)