Results 1 to 4 of 4
  1. #1
    Mercer999 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    11

    How to sum in a form whose query returns no records

    I'm having a hard time with this. The main form holds multiple subforms, each of which have a sub total, which is then totaled in a textbox on the mainform. The forms are linked using ms access parent child link functionality. My question is, how do you handle the case where one or more subform queries return zero records?

    I've tried the NZ() function at multiple levels (from subform query textbox, to the main form summation textbox.).

    I've tried to use VBA to DCOUNT the records in the Query, but that leads to another problem:
    • The formula is in the textbox control source, so using vba to set textbox to zero throws an error of it's own.


    I've tried to capture the error code, but still run into the prior issue.

    What is the standard practice way of achieving a simple summation across forms and handling zero records on a subform query? I feel like this would be a common occurrence.

    There are screenshots below showing a form example:



    Click image for larger version. 

Name:	Screenshot_23453.jpg 
Views:	12 
Size:	252.2 KB 
ID:	40703Click image for larger version. 

Name:	Screenshot_34552.jpg 
Views:	12 
Size:	275.7 KB 
ID:	40704
    Attached Files Attached Files
    Last edited by Mercer999; 01-17-2020 at 01:57 PM.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So 2nd form has no records as why the value is 0? Might could try IIF(Sum([UnitPrice])=0, 0, Sum([UnitPrice]) Not sure if 0 is getting returned so might try "" or null in stead of =0.

  3. #3
    Mercer999 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    11
    I added another couple screenshots. These are examples showing 2 examples. In all cases, the form query returns 0 records. In case 1, Allow Additions is enabled, and the main form calculation works as intended. When Allow Additions is turned off (case 2, form CF2), then the subform subtotal returns NULL, which is not captured by the NZ() function. In my database, the queries are totaling rows, so you cannot add records.

    So the New Record row in the dataset when the Allow Additions option is 'yes' allows the calculated textbox to work. I've tried to use VBA to DCOUNT the records in the Query, but then I have the issue of a formula in the textbox. I've tried using =IIf(Sum([PY3]) Is Null,0,Sum([PY3s])) .. Tried checking if isNumeric. . nothing seems to work and I get #Type! returned. Please let me know if more info is needed. I've spend a lot of time trying correct this so any help is much appreciated.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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. Parameter query that returns no records
    By Roncc in forum Access
    Replies: 29
    Last Post: 01-07-2018, 06:01 PM
  2. Replies: 2
    Last Post: 04-21-2017, 01:15 PM
  3. Replies: 3
    Last Post: 05-14-2015, 04:17 PM
  4. SELECT TOP 10 Query returns 12 records
    By Paul H in forum Queries
    Replies: 8
    Last Post: 09-11-2013, 03:38 PM
  5. form returns records not in record source
    By aussie92 in forum Forms
    Replies: 2
    Last Post: 01-14-2013, 11:23 AM

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