Results 1 to 5 of 5
  1. #1
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55

    Question Sum Multiple Dates in Report

    I have a table that captures up to 10 dates and 10 memos (one for each date). I am trying to create a query/report that sums that amount of time a date shows between a range.

    Example:

    Record #1:
    Date#1: Jan/02/2016 Memo: some memo
    Date#2: Jan/05/2016 Memo: some memo
    Date#3: Jan/30/2016 Memo: some memo
    Date#4: Feb/02/2016 Memo: some memo

    Record #2:
    Date#1: Jan/03/2016 Memo: some memo


    Date#2: Jan/10/2016 Memo: some memo
    Date#3: Feb/08/2016 Memo: some memo

    If I picked the dates Jan/01/2016 to Jan/31/2016, the report would show 5. (5 dates are in Jan)

    I hope that's pretty clear. I am in the midst of creating the query and have pulled all 10 date fields but now I am stuck. Do I display them all then have some sort of SUMIF on the report?

    Thanks,
    Robb

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    something like

    SELECT Format(Date#,"yyyymm") AS YearMonth, Count(*) AS NoofMemos
    FROM myTable
    GROUP BY Format(Date#,"yyyymm")

  3. #3
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    This looks very promising. I have been doing some digging and I am looking at using an IIf in the report to count the dates. I've realized it's more of a count than a sum. On a side note I've tried this in a text box of the report footer:

    =IIf([WaitList1] Between [Forms]![frmOptions]![StartDate] And [Forms]![frmOptions]![EndDate],Count(*),0)

    I was thinking I could do this for each date field. Issue is, it seems to count all the records and ignores the in between.

    Question regarding your response. I have never built a query using s SELECT statement, do just past that into an SQL view of my query?

    Table is: tblActivity
    Date fields are: WaitList1, WaitList2 etc...
    It takes on dates from a form: Between [forms]![frmOptions]![StartDate] And [Forms]![frmOptions]![EndDate]

    How would that look in your statement?


    Quote Originally Posted by Ajax View Post
    something like

    SELECT Format(Date#,"yyyymm") AS YearMonth, Count(*) AS NoofMemos
    FROM myTable
    GROUP BY Format(Date#,"yyyymm")

  4. #4
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    Ok I figured out how to count if the dates are between the selected dates using:

    =COUNT(IIf([WaitList1] Between [Forms]![frmOptions]![StartDate] And [Forms]![frmOptions]![EndDate],1,null))

    I used it for each WaitList field and it works like a charm.

    I am still very interested in how the query would work since I would prefer to do this with a query than on a report.

  5. #5
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    do just past that into an SQL view of my query?
    yes, but change names to match your table and field names

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

Similar Threads

  1. Replies: 1
    Last Post: 06-09-2014, 02:04 PM
  2. Replies: 1
    Last Post: 10-21-2013, 09:42 AM
  3. Replies: 10
    Last Post: 08-29-2013, 09:21 PM
  4. Multiple Min/Max dates by sequential dates
    By chucku in forum Access
    Replies: 2
    Last Post: 04-05-2012, 07:43 AM
  5. Replies: 3
    Last Post: 09-29-2009, 07:08 AM

Tags for this Thread

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