Results 1 to 9 of 9
  1. #1
    jtmott is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2013
    Posts
    17

    Return "0" when no results are found.

    I have a query that that sums up the number of entries in the last hour. The problem I have is when there are no entries with that time frame I get no results and I need it to return "0" if that is that case.


    I have 2 tables in the query and I'm using left join. I'm also using NZ() command in the field as well.
    Any help would be appreciated.
    Click image for larger version. 

Name:	Query.png 
Views:	26 
Size:	14.4 KB 
ID:	35855

  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 table of tHours.
    8:00 am
    9:00 am
    etc

    add this table to your query in an outer join.
    show ALL recs in tTime and some in your tables
    show tTime.Hour and Sum(Nz(Hits_Qty),0)

    now you will get every hour , and 0 for nulls.

  3. #3
    jtmott is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2013
    Posts
    17
    Thanks for the reply. Unfortunately it didn't work or I did something wrong.

  4. #4
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    Hi, I am no expert but can you not use the SUM iif statement in access? SUM(IIF(condition, valueiftrue, valueiffalse))..
    so something along the lines of SUM(IIF(SUM(CPQTY),0,0,SUM(CPQTY)))

    Then again maybe you don't need a sum iff but just the iif...... IIF(SUM(CPQTY),0,0,SUM(CPQTY))

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't think your date criteria makes sense.

    between dateadd("h",0,now()) and now()+1

    dateadd("h",0,now()) is adding nothing to now - so the value is now (this time)
    now()+1 is adding 1 day to now - so the value is this time tomorrow

    which is clearly not 'in the last hour'

    try

    between dateadd("h",-1,now()) and now()

    also Date is a reserved word, using it as a field name can produce strange results. recommend change it to something more meaningful such as perhaps 'hitDate'

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    With some changes, like replacing "-" with "_" in table/field names, and using field RepTime in format "Genereal Date" (reporting time in table Hits_Qty_Complete will contain date AND time):

    1. You need a list of departments. Either you have a table of departments, or you can use a saved query like
    Code:
    qDep = SELECT DISTINCT dep.Department FROM Dept_WrkCtr dep;
    2. You need a saved query which returns quantities by department reported during last hour.
    Code:
    SELECT wc.Department, Sum(Nz(rep.CPQty,0)) AS [Hits Qty], (Now()-1/24) AS FromTime, Now() AS ToTime
    FROM Dept_WrkCtr AS wc LEFT JOIN HITS_Qty_Complete AS rep ON wc.WorkCenter = rep.CPWrkc
    WHERE (((rep.RepTime)>Now()-1/24))
    GROUP BY wc.Department;
    3. You create a query, which returns quantities reported during last hour for all departments
    Code:
    SELECT qDep.Department, Nz(qHitsQty.[Hits Qty],0) AS [Hits Qty], qHitsQty.FromTime, qHitsQty.ToTime
    FROM qDep LEFT JOIN qHitsQty ON qDep.Department = qHitsQty.Department;
    P.S. Probably it will be better to create an udf for getting time some time units (days/hours/minutes/etc.) earlier/later than NOW(). The expression I used works OK, but Access is trying to interpret it as date, and to replace it in query sometimes. When you use the expression, then whenever the query returns weird results, check WHERE clause of qHitsQty.
    Last edited by ArviLaanemets; 10-18-2018 at 01:22 AM. Reason: remark

  7. #7
    jtmott is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    I set up a query separate query for all Departments which allowed me to use NZ().
    The data resides on a system that is in the Eastern time zone and I'm in Central which is why the query is looking from current time +1 hour.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    which is why the query is looking from current time +1 hour.
    as previously explained - now()+1 is adding 1 day to now, not 1 hour

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by jtmott View Post
    The data resides on a system that is in the Eastern time zone and I'm in Central which is why the query is looking from current time +1 hour.

    1 hour = 1/24 days!

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

Similar Threads

  1. Replies: 13
    Last Post: 12-12-2016, 12:26 AM
  2. Replies: 6
    Last Post: 11-18-2013, 07:52 AM
  3. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  4. Replies: 3
    Last Post: 06-29-2012, 08:54 AM
  5. Replies: 4
    Last Post: 03-23-2012, 01:18 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