# Thread: Calculations with criteria (2000)

1. ## 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. ## 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. ## Re: Calculations with criteria (2000)

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

4. ## 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. ## 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. ## 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. ## 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. ## 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
•