Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: last date of quarter or month

    if you have a date in a query, how do you calculate the last date in a given period? For example if the date in the query was 1st Jan, you can get the result of 31st Jan if you want the last date of the month, or 31st march if you want the last date of the quarter? Also, if you have the date 10th Feb, if you want the 1st date of the month you get 1st Feb, or the first date of the quarter you get 1st Jan?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There might be an easier way:

    Code:
     
    DateAdd("m",DateDiff("m",0,MyDate)-1,2) AS BeginningOfMonth
    Code:
    DateAdd("d",-1,DateAdd("m",DateDiff("m",0,MyDate),2)) AS EndOfMonth
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *adds to library* that may come in handy
    Clever chap!

    Oh and nice postcount
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I had a search in t'interweb - there are a few alternatives, most involving dateserial. I don't think they look any more efficient or easy to use.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    *adds to library* that may come in handy
    If you have ever seen the preferred way to strip the time off a SQL Server datetime column (i.e. using dateadd and datediff rather than convert) then you have seen this technique before. The only difference is accounting for Access's "zero" date and working in months rather than days.

    Take the algorithms apart until you understand how they work and you will find you don't need to keep them in your library - you can make up your own.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I don't know if this simpler or simply confusing but I tend to use this

    YourDate - Day(YourDate-1) as FistOfMonth
    DateAdd("m", 1, YourDate - Day(YourDate-1)) - 1 AS LastDayOfMonth

    I'm not sure if this is considered good practice, but it works for me ?



    MTB

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by MikeTheBike
    YourDate - Day(YourDate-1) as FistOfMonth
    roflmao
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Whilst we're on the topic, I have a quick date question:
    In JavaScript you can create a date from specified dateparts
    Code:
    var d = new Date(1, 12, 07)
    Is there an equivalent to this in VBA?
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MikeTheBike
    YourDate - Day(YourDate-1) as FistOfMonth
    Oh yeah - I saw that one on my web trawl. Yes - that is much simpler than mine.
    Although doesn't it need to be:
    Code:
     
     YourDate -(DayYourDate)-1)
    (Try the first of Jan to see if you agree).

    The onlyother thing is it looks less easy to convert for quarters. And maybe years....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Pootle

    No it should be

    MsgBox YourDate - Day(YourDate) + 1 as FirstOfMonth
    DateAdd("m", 1, YourDate - Day(YourDate) + 1) - 1 as EndOfMonth

    which is what I actually normally use but, for some reason, I changed it because it looked simpler. I should know ANY change needs testing THOUGHLY !!

    You would have thought I would have learnt by now.
    And yes for quarter or any thing else its no much use, but reporting by month crops up a lot.


    BTW georgev

    "roflmao" ? being a wrinkly I'm not sure wheather I should laugh or be insulted ?

    rofl is on my crib sheet but mao seem to be missing !!


    MTB

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe
    Quote Originally Posted by MikeTheBike
    rofl is on my crib sheet but mao seem to be missing !!
    mao = my a$$ off
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    those would work providing you are using a 'real' calendar ie quarter 1 ends say March 31 every year, as opposed to an accounting calendar which could end anytime (especially if the organisation has leap years or a specified number of periods per year eg 13 x 4 weeks). if you are going down that route then the only workable route is a financial year table which identifies either the start of a period or the end of an accounting period.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Although doesn't it need to be:
    Code:
     
     YourDate -(DayYourDate)-1)
    Quote Originally Posted by MTB
    No it should be

    MsgBox YourDate - Day(YourDate) + 1 as FirstOfMonth
    LOL

    Here's how I keep up with the kids:
    http://www.tasgreetings.com/chat.htm
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    BIBO -- Beer In, Beer Out
    Hahahahaha!
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Hahahahaha!
    personally Im all in favour of AIWO

    Alcohol In / Water Out
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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