Results 1 to 3 of 3
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    40

    Summarizing data by day range of selected month


    Hi!

    I and trying to summarize the hours worked by the 1st - 15th and the 16th - end of whatever month is entered in the parameter. I am not sure if I am on the right track at all but this is what I was trying to make work and the sql of the current query.

    Code:
    1st - 15th: IIF(Datepart("d",<=15,Sum([Hours]),0))
    The above formula gives me an error so I had to take it out of the below SQL.

    Code:
    SELECT tbl_WorkSummary.EmployeeName, Sum(tbl_WorkSummary.[Hours]) AS [1st - 15th], Sum(tbl_WorkSummary.[Hours]) AS [16th - 31st]
    FROM tbl_WorkSummary
    WHERE (((tbl_WorkSummary.DateWorked) Between DateSerial(Year(Date()),[Enter month],1) And DateAdd("d",-1,DateAdd("m",1,DateSerial(Year(Date()),[Enter month],1)))))
    GROUP BY tbl_WorkSummary.EmployeeName;
    
    Thank you for your time and help!

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    try

    sum(IIF(Datepart("d",dateworked)<=15,[Hours],0))

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    40
    You are the best! Thank you so much!

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

Similar Threads

  1. Replies: 3
    Last Post: 12-29-2014, 10:14 AM
  2. Replies: 5
    Last Post: 10-08-2014, 02:23 PM
  3. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  4. Replies: 5
    Last Post: 05-14-2012, 02:01 PM
  5. Replies: 1
    Last Post: 05-25-2010, 02:58 PM

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
  •  
Tech Forums: Microsoft Office Forums