Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: previuse month minus 4 months

    Hello,

    I need help with a query.
    Based on the [DateRecieved] field (DataTYpe - Date/Time)
    I need to recieve a data between prev month (July) minus 4 months.
    So that I may see Apr, May, June and July data.

    Thank you

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    given that we are somewhere in august as i type this.
    the '0th' day of this month is therefore the last day of july
    the 1st day of this month minus four months is the first day of April

    ? dateadd("d", -1 * day(now()),now())
    31/07/2007 17:37:38

    ? dateadd("m", -4, dateserial(year(now()), month(now()),1))
    01/04/2007
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    better for end-july is

    ? dateadd("d", -1 * day(date()),date())
    31/07/2007

    which kills the time componant

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Thank you so much but I still not sure where should I insert my field name [DateRecieved], based on which I may rec. data.

    Thank you.

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Is this in a form? report?
    Ryan
    My Blog

  6. #6
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    the field is in select query


    Select id, name, status, [Date Recieved]
    from table name

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    How about the simple:

    Code:
    SELECT * FROM YourTbl WHERE Month(YourTbl.DateReceived) >= (Month(Date()) - 4)
    Sam

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't know how or where you are trying to do whatever you are doing.

    let me guess you are in query-design view.

    in the criteria box of your date field have a go with something like
    Code:
    Between DateAdd("m",-4,DateSerial(Year(Now()),Month(Now()),1)) And DateAdd("d",-1*Day(Date()),Date())
    izy


    LATER: oooops - now i know how & where - answer still applies.
    Sam's suggestion will also fly but will include data for august
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    correction: Sam's wont fly!

    it will return stuff for April thru December for 2006, 2005, 2004, 2003 etc etc etc AND stuff from august 2007

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    you are right, i am workin via query-design.
    I just inserted your code and it is working.

    Thank you so much

  11. #11
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're right, ir, it won't, and for another reason. If he's in January, he'll get a tidy error message that there's no month that has a number -3.

    So it's back to the drawing board for me, I'm afraid.

    Sam

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is datediff part of VBA, or is it just limited to VB & VB.NET?
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    datediff() is deep inside VBA

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    is datediff part of VBA, or is it just limited to VB & VB.NET?
    In fact I believe it is in VB.NET only as a backward compatability\ concession to VB programmers so they don't have to learn the OO way of doing things. Or at least I think so - I haven't got VS.NET installed on this 'station yet so can't check but I think it is a method of the date object.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    ? dateadd("d", -1 * day(now()),now())
    31/07/2007 17:37:38
    Izy - I think that is a quite superb way to get the end of the previous month.

    One minor thing - are you sure you want to simply get rid of the time component when you use date()? What about stuff that falls on the 31st July?

    Should the OP require the end of the current month then (adapted for Access from here: http://www.sqlmag.com/Blog/index.cfm...artmentID=1016):
    Code:
    ?dateadd("m", datediff("m", 1, date()), 1)
    Still has the time problem....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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