Results 1 to 6 of 6
  1. #1
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30

    Sum with nested if to a subform footer

    Hello!

    I'm trying to perform a sum to a form footer in order to transfer the value to the main form. Tricky part is that an iif function is being used to the subform body. When trying to nest the iif to the sum I'm getting #Error as a value from the main form field so I presume that I can't nest iif under sum. Also I can't use the value of the already calculated field from the subform.

    In detail the following take place.

    Subform:

    [Price] : gets a value from a table
    [Price2]: =((IIf([Cover]=No;[Price];[Price]+200))*((100-[Discount])/100)*[QTY])

    The above work without issues.

    Subform footer:

    [SumItems]: =Sum((IIf([Cover]=No;[Price];[Price]+200))*((100-[Discount])/100)*[QTY])




    If i change (IIf([Cover]=No;[Price];[Price]+200) to [price2] access can't do the calculation so i always get 0 as value to the field of the form. Any ideas regarding a workaround?

    Thanks!

  2. #2
    Mickjav is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    You say price2 works so why not just do this in the controlsource of a text box on main form =[SubformName].[Form]![Price2]

  3. #3
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    The problem with that option is that i need to get back to the form a total of price2 amount, meaning a sum is needed prior transfering. This is why i performed the sum in the footer of the subform and then transfered it to the main form.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Move the calculation to the subforms record source, have a look at the attached example.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Sorry for the delay.. Correct and helpful as always! Many thanks!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome, good luck with your project and stay safe!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. getting #Name? in a nested subform!!
    By PDilly in forum Forms
    Replies: 4
    Last Post: 07-17-2019, 02:32 PM
  2. Replies: 3
    Last Post: 06-29-2017, 03:02 PM
  3. Subform Footer Calculation
    By bsc0617 in forum Forms
    Replies: 21
    Last Post: 08-29-2013, 02:28 PM
  4. Nested Subform's control events
    By amrut in forum Forms
    Replies: 3
    Last Post: 01-10-2013, 02:19 PM
  5. Delete record from nested subform
    By raton in forum Forms
    Replies: 1
    Last Post: 04-14-2012, 11:39 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