Results 1 to 9 of 9
  1. #1
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58

    Exclude a set of products when using a sum function in expression Builder

    Hi there,



    I have a form with a set of products and deals.
    I am trying to add a field in the footer of the form to show the sum of deals.
    But I need to exclude few products from adding up.
    Can somebody help with the formula in the expression builder?

    Below is the set of products I use.

    ProductName Deals
    Financed 10
    Leased 1
    Service Contracts 7
    GAP 6
    VTR / Etch 16
    Cilajet 1
    Security 0
    Maint. 1
    Key 0


    TIA,
    Mismag
    Attached Thumbnails Attached Thumbnails Deals.png  

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I'd probably sum a hidden control that would hold the value if the product needed to be summed?

    Or add an IIF() function to that builder to sum value or 0 depending on your condtition?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    DSum("Deals","yourTable","ProductName <> 'GAP'") ?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Micron View Post
    DSum("Deals","yourTable","ProductName <> 'GAP'") ?
    The problem with that approach is it *might* not accurately reflect the rows filtered in the form's dataset if the user is allowed to filter it.

  5. #5
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Quote Originally Posted by kd2017 View Post
    The problem with that approach is it *might* not accurately reflect the rows filtered in the form's dataset if the user is allowed to filter it.
    Exactly..I was about to reply to the other post and I got ur reply @kd2017.

    Dsum is giving me the total sum of products.

    The sum should be calculated after I enter the information in the form.
    Any help would be greatly appreciated.

    TIA,
    Mismag
    Attached Thumbnails Attached Thumbnails Form.png  

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see a problem with DSum if you apply the correct criteria and put the expression in the right place. If you put the expression in a control in the form footer and then filter the form, the sum won't change because the expression is doing the same thing as the form filter. That's because it's not based on the control containing the expression - DSum is applied to the table field. However, I didn't mean to show a control with a "filtered" sum and still show the complete set of records. That could be misleading although it would work. If you apply a filter in form code, use the same criteria for the expression.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, pictures instead of 1,000 words -
    expression in control calculates 20 (based on values from field that Text11 is bound to) [DSum expression is based on first name being Theresa]
    Click image for larger version. 

Name:	FilteredDSum.jpg 
Views:	18 
Size:	15.4 KB 
ID:	44411


    here's the filter being applied. Note that there are 17 records.
    Click image for larger version. 

Name:	FilteredDSum2.jpg 
Views:	18 
Size:	26.2 KB 
ID:	44412


    Filter applied, record count now 10, sum control still shows 20.
    Click image for larger version. 

Name:	FilteredDSum3.jpg 
Views:	18 
Size:	15.7 KB 
ID:	44413

    The defense rests.
    Last edited by Micron; 02-25-2021 at 05:01 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Add a new field in the recordset of the form with this expression:
    Code:
    TheDeal: IIf([ProductName]="GAP",0,1)*[Deal]
    Then, use this field ([TheDeal]) in SUM of your form.

    You can add (exclude) more ProductNames in IIF statement using the OR operator.

    Cheers,
    John

  9. #9
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Thanks to all the inputs.
    This formula worked for me =Sum(IIf([ProductName]<>"Financed" And ([ProductName]<>"Leased"),[Deals]))

    Thanks & Regards,
    Deepika

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

Similar Threads

  1. Replies: 5
    Last Post: 08-17-2020, 08:43 PM
  2. Use custom function in Expression Builder
    By MattLewis in forum Programming
    Replies: 3
    Last Post: 01-17-2018, 10:54 PM
  3. Expression Builder - Function DateDiff
    By geraldk in forum Access
    Replies: 2
    Last Post: 04-01-2015, 12:08 PM
  4. iff function in expression builder
    By deso in forum Queries
    Replies: 4
    Last Post: 03-21-2013, 11:20 PM
  5. IIf Function for expression builder
    By Bertrand82 in forum Programming
    Replies: 3
    Last Post: 11-12-2012, 12:34 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