Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    End Of Month query as a function (2k/XP)

    This is probably elementary for most of you, so please forgive my simple mindedness. I have a query that calculates the end of month for various reports and queries. However, whenever I use it in a query, I always have to substitute the date field in two places. Is there a way to create a function that could be called from any place in the database to calculate the end of month requested?

    SELECT DateSerial(Year([dtmDate]),Month([dtmDate])+1,1)-1 AS EOM
    FROM MyTable;

    As usual, thanks for your ideas.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: End Of Month query as a function (2k/XP)

    You could create a function that accepted a date argument and return an EOM date, then just call that function in a query or wherever.

    <pre>Public Function EOMDate(ByVal dtmDate As Date) As Date
    Dim dtmReturn As Date

    dtmReturn = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
    EOMDate = dtmReturn
    End Function</pre>


    The reason this works is because the end of month for the date passed in is the same as the zeroth day of the next month.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End Of Month query as a function (2k/XP)

    Create a new standard module, and copy the following code into it:

    Public Function EndOfMonth(varDate As Variant) As Variant
    If VarType(varDate) = vbDate Then
    EndOfMonth = DateSerial(Year(varDate), Month(varDate) + 1, 0)
    Else
    EndOfMonth = Null
    End If
    End Function

    Use like this:

    SELECT EndOfMonth([dtmDate]) As EOM
    FROM MyTable;

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: End Of Month query as a function (2k/XP)

    Charlotte,

    You and Hans are incredible! Both approaches accomplished EXACTLY what I was seeking!

    THANK YOU BOTH!

Posting Permissions

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