Results 1 to 5 of 5
  1. #1
    tmanDuer is offline Novice
    Windows 10 Access 2002
    Join Date
    Sep 2017
    Posts
    9

    Trying to bring back last 8 quarters from a specific date...

    hello all!



    Code -

    SELECT DatePart('yyyy',[dateSold]) AS yrs, DatePart('q',[dateSold]) AS qtrs, Count(IIf([Product]="Yes",1,Null)) AS Product, Count(IIf([Product="No",1,Null)) AS [Non-Product]
    FROM ART
    WHERE (((ART.DateSold)>=DateAdd("q",-9,[Enter Date])))
    GROUP BY DatePart('yyyy',[dateSold]), DatePart('q',[dateSold])
    ORDER BY DatePart('yyyy',[dateSold]), DatePart('q',[dateSold]);

    This works fine if I enter today's date, but if I enter a date from the beginning of the year like '3/31/2017' it brings back the previous 9 quarters from that date(nice), but also all quarters of 2017.

    What am I missing here - > WHERE (((ART.DateSold)>=DateAdd("q",-9,[Enter Date])))

    Thank you!
    TD

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There is no "less than" - if DateSold > the past 8 quarters will bring all dates from that date on. Less than date entered?

  3. #3
    tmanDuer is offline Novice
    Windows 10 Access 2002
    Join Date
    Sep 2017
    Posts
    9
    Yes, less than date entered. My mistake. I was trying different things.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of asking for DAteAdd("Q",....)
    I have the form set the dates...user enters Q1, 2017, and the form sets 2 text boxes:
    txtStartDate = 1/1/2017
    txtEndDate = 3/31/2017

    then the query ALWAYS pulls startdate,endDate.

  5. #5
    tmanDuer is offline Novice
    Windows 10 Access 2002
    Join Date
    Sep 2017
    Posts
    9
    Thank you, ranman, I appreciate help and your time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-09-2015, 06:47 AM
  2. Replies: 9
    Last Post: 06-23-2015, 03:13 PM
  3. Replies: 1
    Last Post: 01-16-2015, 12:52 PM
  4. Replies: 5
    Last Post: 04-23-2013, 03:22 PM
  5. Bring back focus to a form
    By AbbHeDa in forum Programming
    Replies: 6
    Last Post: 04-28-2012, 01:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums