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.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > End of Month in Query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
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
Reply With Quote
  #2 (permalink)  
Old
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.
Reply With Quote
  #3 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,086
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
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old
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.
Reply With Quote
  #5 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
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.)
__________________
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:54.
Reply With Quote
  #6 (permalink)  
Old
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!
Reply With Quote
  #7 (permalink)  
Old
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)
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2008
Posts: 4
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On