Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20

    Unanswered: End of Month Query

    Hello,

    I need to write some MS Access SQL that selects a date from a field of dates that equal the end of month for that date. For example:

    1. If a date is 25th of June it is not selected.
    2. If a date is the 30th of June it is selected.
    3. If the date is 28th of May it is not selected.
    4. If the date is the 31st of May it is selected.

    The dates can be any date in a calendar year and are date types not text types.

    Unfortunately I cannot use a user defined function, which would be an easy option, because I need to link this query to an Excel spreadsheet. And as I found out JET cannot reference a UDF when it’s run through a link.

    Any advice (and a lot of alcohol) would be appreciated.

    BBB

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    SELECT *
    FROM table
    WHERE ((Date)=#5/31/2008# Or (Date)=#6/30/2008#)); etc etc

  3. #3
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Not sure....

    I'm not sure if your question has been answered or that I really understand it well enough to comment, but here I go anyway.

    If you create a simple 12 record table with all of the month-end dates then you can put that in your query with an inner join on your table and it will only pull the records where the dates are equal.

    My 2 cents!

    Stu
    --If its free, take it for what its worth!

  4. #4
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20
    Thanks for your reply.

    Yes your right. The simple answer here is to create a list of end of month dates and join them to the table with the date field.

    I was hoping for some SQL method of deriving the end of month.

    Regards
    BBB

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Last day of the month? No probs!
    Code:
    SELECT DateAdd(dd, -1, DateAdd(mm, DateDiff(mm, 0, <your date>), 0))
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20
    Quote Originally Posted by HelpMePlease
    SELECT *
    FROM table
    WHERE ((Date)=#5/31/2008# Or (Date)=#6/30/2008#)); etc etc

    Nice idea but what if the year is 1995 through to 2008?

  7. #7
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20
    Quote Originally Posted by georgev
    Last day of the month? No probs!
    Code:
    SELECT DateAdd(dd, -1, DateAdd(mm, DateDiff(mm, 0, <your date>), 0))
    George,

    Big fan of the DateAdd function I use it alot but I am not sure it will work through a link to Excel which is what I need. As I understand it Jet can't reference the VBA library when called through ODBC or ADO.

    Good suggestion though.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DateAdd is available in JET(SQL), not just in VBA.
    Give it a go for yourself.

    The example I provided won't work right off the bat, because of syntax differences in Access, but it will get the job done
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20
    George,
    Thanks for the added info. I'll will try it out tommorrow at work.

    Do you know why this function would work? Is it because its a built in function?
    Last edited by blue_bottle_boy; 07-02-08 at 10:37.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If nothing else, you can use whatever functions you want, including VBA and spit the results out to another table and then use Excel to pick up that table.

    Just a BTW really
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20

    Solution

    Just posting a follow up for anyone who is interested.

    Using George's recommendation above I managed to get a solution.

    DateAdd("m",DateDiff("m",0,[Your Date Here]),1)



    I replicate it below in an example query.

    SELECT Orders.OrderDate
    FROM Orders
    WHERE (((Orders.OrderDate)=DateAdd("m",DateDiff("m",0,[OrderDate]),1)));


    The trick is that DateAdd and DateDiff function calculate slightly differenty. One must start its index from 0 whilst the other must start from 1. In any case, if you use the formula as shown you should calculate the end of month date from a given date.

    Regards

    BBB
    Last edited by blue_bottle_boy; 07-03-08 at 09:30.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'll break it down for you
    Code:
    DateAdd(dd, -1, DateAdd(mm, DateDiff(mm, 0, <your date>), 0))
    Starting from the middle and working outwards
    Code:
    DateDiff(mm, 0, <your date>)
    This works out the difference in months between your date and the base date (being represented as zero here).

    You then add this number of months on to the base date again
    Code:
    DateAdd(mm, DateDiff(mm, 0, <your date>), 0))
    This gives you the 1st day of the given month because we're only adding months on from zero, effectively truncating any datepart lower than the month (i.e day, hour, minute, second, millisecond).

    Finally we remove one day from the first day of the month found above to give us the last day of the previous month
    Code:
    DateAdd(dd, -1, DateAdd(mm, DateDiff(mm, 0, <your date>), 0))
    Hope this helps explain what's going on
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20
    George and Everyone,


    This is a comparison between George's calc and mine.

    Mine: DateAdd("m",DateDiff("m",0,[OrderDate]),1)

    George: DateAdd("d",-1,DateAdd("m",DateDiff("m",0,[OrderDate]),0))

    Qry:
    SELECT
    Orders.OrderDate, DateAdd("m",DateDiff("m",0,[OrderDate]),1) AS Mine, DateAdd("d",-1,DateAdd("m",DateDiff("m",0,[OrderDate]),0)) AS George
    FROM Orders;



    He are the results

    Order Date ; Mine : George
    31-Jul-1996 ; 31-Jul-1996 : 29-Jul-1996
    30-Sep-1996 ; 30-Sep-1996 : 29-Sep-1996
    31-Oct-1996 ; 31-Oct-1996 : 29-Oct-1996
    31-Dec-1996 ; 31-Dec-1996 : 29-Dec-1996
    31-Jan-1997 ; 31-Jan-1997 : 29-Jan-1997
    28-Feb-1997 ; 28-Feb-1997 : 27-Feb-1997
    31-Mar-1997 ; 31-Mar-1997 : 29-Mar-1997
    30-Apr-1997 ; 30-Apr-1997 : 29-Apr-1997
    30-Jun-1997 ; 30-Jun-1997 : 29-Jun-1997
    31-Jul-1997 ; 31-Jul-1997 : 29-Jul-1997
    30-Sep-1997 ; 30-Sep-1997 : 29-Sep-1997
    31-Oct-1997 ; 31-Oct-1997 : 29-Oct-1997
    31-Dec-1997 ; 31-Dec-1997 : 29-Dec-1997
    31-Mar-1998 ; 31-Mar-1998 : 29-Mar-1998
    30-Apr-1998 ; 30-Apr-1998 : 29-Apr-1998


    Regards

    BBB
    Last edited by blue_bottle_boy; 07-03-08 at 19:33.
    Regards
    BBB

    By day, mild mannered number cruncher.
    By night, caped crime fighter.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmm, I have my doubts with your query - however I have spotted one slight error on my one!

    I have run your formula on some sample data and I get the second day of every month returned.
    Code:
    SELECT termination_date
         , DateAdd(mm, DateDiff(mm, 0, termination_date), 1) As [BBB's]
         , DateAdd(d, -1, DateAdd(mm, DateDiff(mm , 0, termination_date) + 1, 0)) As [George's]
    FROM   leavers
    Please note that I have run this on SQl Server and not on Access; hence the syntax differences


    Results
    Code:
    termination_date                                       BBB's                                                  George's                                               
    ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 
    2005-04-22 00:00:00.000                                2005-04-02 00:00:00.000                                2005-04-30 00:00:00.000
    2001-12-12 00:00:00.000                                2001-12-02 00:00:00.000                                2001-12-31 00:00:00.000
    2007-06-30 00:00:00.000                                2007-06-02 00:00:00.000                                2007-06-30 00:00:00.000
    2003-03-14 00:00:00.000                                2003-03-02 00:00:00.000                                2003-03-31 00:00:00.000
    2007-05-11 00:00:00.000                                2007-05-02 00:00:00.000                                2007-05-31 00:00:00.000
    2001-05-01 00:00:00.000                                2001-05-02 00:00:00.000                                2001-05-31 00:00:00.000
    I have added the plus one after re-reading your requirement.
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2008
    Location
    Usually at home
    Posts
    20
    George,

    I'm at a loss to explain why there is a difference between the two applications(by the way my query was on the Microsoft Northwind example database). Maybe it has something to do with the way the libraries files are written I think. Perhaps there is a difference to the way the functions are written?I must admit I don't know enough about SQL server. (I'd like to know more because I am getting into ASP.Net and it would be useful.)

    However, I tested my version of the function out today at work in a MS Access database which has 300 dates and it picked up all month end dates. Also it worked through a link which was what I was needing. Otherwise I would have used a UDF.

    If you find out a reason why this occurs let me know.

    PS I didn't mean adding a plus 1 to my calc in sql server. I meant you should start your index from 1. What happens if you take the +1 out and change the trialing ",0" to ",1" in your example? eg

    DateAdd(d, 0, DateAdd(mm, DateDiff(mm , 0, termination_date) , 1))

    BBB
    Last edited by blue_bottle_boy; 07-04-08 at 08:46.
    Regards
    BBB

    By day, mild mannered number cruncher.
    By night, caped crime fighter.

Posting Permissions

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