Results 1 to 10 of 10
  1. #1
    chippy is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    6

    Sum Function Producing Error

    Two years ago, I made a database for one of our departments that tracks tasks that need to be done. Each task is given a priority between P1-P5 depending on how important it is. I use a query to filter out tasks that are being worked on or are done, and then produce a report that lists all the tasks that still need to be worked on. As a part of this report I sum up each priority and provide a count. I used the following equation to do that;

    Code:
    =Sum(IIf([Item_Query]![Priority]="P1",1,0))
    Now I am doing a very similar thing for a different department and the only difference is that I named the query differently this time to avoid confusion. So my new equation is;



    Code:
    =Sum(IIf([Outstanding_Item]![Priority]="P1",1,0))
    Other than the name nothing is different. Both queries contain the same information and are formatted the exact same way. When I run a report in the first database it spits out the correct count, but when I run it in the new one it spits out #Error. Just for kicks I tried renaming the query, the equation automatically updated for the change but it still spits out #Error. I am not sure what is going wrong, can anyone shed some light on this?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Report uses the query as RecordSource?

    Why include query name in the expression?

    Why build another db? Departments don't use same datasource?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    chippy is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    6
    June,

    To answer your questions;

    1. Yes, I am using my query as the source. Query is filtering out a lot of unwanted items and leaving me with only relevant items.

    2. I can work in Access but I am not super familiar with program. Two years ago I probably looked up online that found that equation. What I saw online had the query name, so I did the same when I made my equation. If there is a better way to do this I am all ears. (Note I tried removing the query name from the equation and it still errored out)

    3. The databases are being used by different departments for different purposes. The task tracking is pretty much identical between the two, but every other aspect is different.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Db is not split design? Are there multiple simultaneous users? Departments use different data?

    See nothing wrong with expression. If you want to provide db for analysis follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    chippy is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    6
    Attached is what I have done. I have stripped out everything except for the relevant table, query, and report.

    Task Count.accdb

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This seems to work:

    Code:
    =DCount("Priority","Outstanding_Item","Priority= 'P3'")

  7. #7
    chippy is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    6
    Yes, the Dcount equation does seem to work. Thanks.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the report is not bound to the query and design is different from the first db. I wil try to look at your db later this week.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the report is not bound to the query and design is different from the first db. I wil try to look at your db later this week.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Finally got a chance to look at db. Just move the Sum calcs to Report header section. Doesn't work in Page sections.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 07-15-2015, 01:30 PM
  2. Replies: 8
    Last Post: 01-31-2014, 01:45 PM
  3. Replies: 4
    Last Post: 10-18-2013, 01:13 PM
  4. Producing A Schedule
    By dylcon in forum Access
    Replies: 11
    Last Post: 06-14-2013, 01:36 PM
  5. Help producing queries
    By skidia in forum Access
    Replies: 1
    Last Post: 05-13-2013, 12:21 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