Results 1 to 8 of 8
  1. #1
    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

  2. #2
    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 *******************************
    Last edited by pkstormy; 08-18-08 at 13:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,445
    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 on the Tiger 800 or the Norton

  4. #4
    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.

  5. #5
    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.)
    Last edited by pkstormy; 08-18-08 at 13:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    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!

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

  8. #8
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •