Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculations with criteria (2000)

    Hi,
    I'm back again. Now I'm trying to create a calculation based on criteria. I have a continous form I'm using as a subform. This subform has calcExtendedPrice field that I want to add up. I want 2 calculations one that will add the calcExtendedPrice filed if ysnCCOrder (yes/no datatype) field is checked and the other when that field is not checked. I tried using an IIF statement but I can't get it to work. My formula is =IIf([ysnCCOrder]=yes,Sum([calcExtendedPrice]),0) all I get is 0. If I change the formula to =No, I get all the values added. I'm sure where my thinking is off.
    Thanks,
    Deb

  2. #2
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations with criteria (2000)

    Hi,
    I believe I solved my problem. I used the Dsum function and it seems to work. Am I on the right track?
    Thanks,
    Deb

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calculations with criteria (2000)

    Actually, either the Sum or DSum function should work, but that's the basic right track.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculations with criteria (2000)

    DSum should work, but in the original expression, your calculation is in the wrong order. It should be

    <code>=Sum(IIf([ysnCCOrder],[calcExtendedPrice],0))</code>

    For each record, get calcExtended price if ysnCCOrder is true, otherwise 0. Then sum the result of this over all records. Your original expression checks ysnCCOrder for the current record only, and depending on its value returns either the sum of ALL calcExtendedPrice, or 0.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations with criteria (2000)

    Thanks Hans. Didn't know I could do it that way. A lessoned learned. [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations with criteria (2000)

    Well i spoke too soon earlier. While using the DSum function I was getting a sum it wasn't the correct one. So I figured out because I had the following: =DSum("[calcExtendedPrice]","qryComponentPartsVendor","[ysnCCOrder]=no") and was referencing the query it was suming ALL the records not just the ones for that particular subform. So I tried to change the query to the subform but it didn't work so I used Hans' suggestion with sum(....) statement and that's working. But just out of curiosity how would I change the DSum statement so it only sums the subforms particular recordset?

    Thanks,
    Deb

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculations with criteria (2000)

    Ah, a subform. That makes the DSum expression more tricky, because you would have to work in the condition that links the subform to the main form. Unlike Sum, DSum is not "aware" of the record source of the (sub)form. The exact expression depends on the field(s) used to link them and their data types. The expression with Sum will automatically operate only on the records in the subform.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations with criteria (2000)

    Thanks Hans. I apprecite it.
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •