Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Sum giving me an error, asking for a parameter or showing blank

    I am trying to sum()



    I keep getting #err in the page footer and nothing in the report footer

    I have records that list and I would like to sum the visible ones (nulls are collapsed).

    Some are different fields however they are all currency fields with the correct amounts.

    Any unbound box I use doesn't seem to work. I have it working fine on another form without canshrink and I am wondering if that is what it is...

    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	34.3 KB 
ID:	14501

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	19 
Size:	46.9 KB 
ID:	14502

    I have tried using =Sum(Nz([txtPaymentDeposit],0)+Nz([txtPayment1],0)+Nz([txtPayment2],0)) with no luck

  2. #2
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    =Sum(Nz([PaymentReceived1st])+Nz([PaymentReceived2nd])+Nz([PaymentDepositReceived]))


    edit:
    arghh spoke to soon - it's calculating collapsed results I just want what is displayed...

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What do you mean nulls are collapsed? If you don't want records with null fields, then exclude them from the RecordSource.
    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.

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    What do you mean nulls are collapsed? If you don't want records with null fields, then exclude them from the RecordSource.
    I can't exclude the records as they will show on the fields - it is why I have canshrink on - I make them null if the dates aren't the same.

    one record might have 3 different payments. e.g. I pull up 5 records with 15 fields however only display 4 (out of the 15) of them because they match the date and then sum those

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did not understand any of that.

    Aggregate functions can only use fields in RecordSource. Maybe you should do the Nz() calcs in query then apply textbox Sum expression to those constructed fields.
    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.

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Did not understand any of that.

    Aggregate functions can only use fields in RecordSource. Maybe you should do the Nz() calcs in query then apply textbox Sum expression to those constructed fields.

    I only want to sum what is visible on the report

    The report brings up all records in that have TradingAsID and that match either datefield1 or datefield2 or datefield3 - it then brings up all the records that follow the criteria.

    Because each of those records show date1, 2, or 3 on the record on the report - I shrink those fields that do not match the date and leave only the fields that match the date

  7. #7
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Did not understand any of that.

    Aggregate functions can only use fields in RecordSource. Maybe you should do the Nz() calcs in query then apply textbox Sum expression to those constructed fields.
    I am guessing because I shrink txtPayment1 - it can't find it to do the sum... and that is why I get a parameter popup...?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is this a report or form? I've never used CanShrink (only CanGrow) so I don't know what issues that can cause. Turn off the CanShrink and see if the error still happens.
    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
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Is this a report or form? I've never used CanShrink (only CanGrow) so I don't know what issues that can cause. Turn off the CanShrink and see if the error still happens.
    It made no difference so that is ruled out...

  10. #10
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    how I see it summing in my mind is

    sum( record1 txtbox1 + record1 txtbox2 + record2 txtbox2 + record4 txtbox3 )

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did you try the Nz() calcs in query and Sum on those calculated fields as suggested?

    I don't understand your data and form/report design so really shooting in the dark here.
    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.

  12. #12
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Did you try the Nz() calcs in query and Sum on those calculated fields as suggested?

    I don't understand your data and form/report design so really shooting in the dark here.
    yeah I tried nz() - does that work for names of textboxes or just the recordsource?

    If this makes it easier - you can see the fields in teacher table.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	88.7 KB 
ID:	14511

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Nz() used in textbox can reference fieldname or textbox name.

    However, aggregate functions (Sum) can only reference fieldnames.

    Sorry, the image does not help.
    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.

  14. #14
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    I added a field on each record

    =IIf(IsNull([txtPaymentDeposit]),0,[txtPaymentDeposit])+IIf(IsNull([txtPayment1]),0,[txtPayment1])+IIf(IsNull([txtPayment2]),0,[txtPayment2])

    edit the above shouldnt sum as I havent summed anything

    and it sums that fine on each record in the detail section of the report

    but then I want to sum that textbox on the footer I get an error (I am guessing you can't sum a calculated textbox)

  15. #15
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Nz() used in textbox can reference fieldname or textbox name.

    However, aggregate functions (Sum) can only reference fieldnames.

    Sorry, the image does not help.
    How can I sum if it can't read only what's visible (text boxes)

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 09-21-2013, 04:53 AM
  2. Linked Tables Giving Error Message
    By 18ck in forum Access
    Replies: 2
    Last Post: 11-23-2012, 06:30 AM
  3. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 11:40 AM
  4. DLookUp function giving invalid use of null error
    By shubhamgandhi in forum Programming
    Replies: 4
    Last Post: 07-21-2011, 06:04 PM
  5. NoData() still giving me an error.
    By cowboy in forum Programming
    Replies: 3
    Last Post: 04-08-2010, 12:26 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