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

    Need help with DateAdd function bringing back a record of '0' even if there is not data

    Hello, I hope my 'long title' wasn't too confusing...



    I have a dateadd function of '>=DateAdd("q",-8,Date())'

    SELECT DatePart('yyyy',[datepurchased]) AS yrs, DatePart('q',[datereported]) AS qtrs, product FROM Cust
    WHERE (((datepurchased)>=DateAdd("q",-8,Date())))

    There was nothing purchased in the 1st Qrt of this year but there were products purchased in previous quarters and after.

    The graph I built of course does not bring and data back for the first quarter. Is there a way to bring in '0' for this?
    Please let me know if you would like me to clarify.

    Thanks for any help you can provide!

    TD

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a qsAllQtrs query. In this it will do just that ,show a list of every quarter. My dates are 1/1/17, 4/1/17, 7/1/16,10/1/17.

    Take this query and add it into your query. Join the dates together BUT make it an OUTER join.
    (dbl-click the join line)
    set ALL records in qsAllQtrs
    some records in qsYourQry

    bring down qsAllQtrs.Date into the query grid.
    Now you will get EVERY quarter even if it did not have data.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Might be able to do something with a nested query inside yours but not sure. Few others on here have more experience with that.

    Or best bet might be to use a temp table (Year, Quarter, Product) and loop through each of the past 8 quarters, check if the datepurchased is within that quarter and if so, write the product value. If datepurchased not in the quarter then write a 0 for product. Then base output on the temp table.

  4. #4
    tmanDuer is offline Novice
    Windows 10 Access 2002
    Join Date
    Sep 2017
    Posts
    9
    Thank you, Ranman256, that did the trick!

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

Similar Threads

  1. DateAdd to add back weekends and holidays
    By vbagwell1967 in forum Queries
    Replies: 4
    Last Post: 06-20-2017, 07:38 PM
  2. Dateadd function
    By JackieFeng in forum Access
    Replies: 2
    Last Post: 05-02-2016, 10:48 AM
  3. used function DateAdd
    By azhar2006 in forum Queries
    Replies: 2
    Last Post: 10-07-2015, 03:31 PM
  4. Replies: 1
    Last Post: 03-31-2014, 11:10 AM
  5. Help Please - DateAdd Function
    By graviz in forum Queries
    Replies: 3
    Last Post: 03-02-2010, 02:34 PM

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