Summing Field based on criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form named checks and a subform named expense. In the subform I
have a "Yes/No" field and a Total Cost Field. What I am trying to do is to
have a text box in the form footer on the main form that will sum up the
Total Cost field whenever the "Yes/No" field is checked to Yes. I've looked
around and couldn't find a similar question. Thank you for your help in
advance.
 
Mr. Browne
Thank you for your response.

I tried what you had suggested, here is my code
=Sum(IIf([exppaid],[Total Cost],0))
exppaid is the name of the "Yes/No" field.

However when I tested it out there was a "#Error" in the text box. I was
wondering if it is because this syntax is looking into the main form (where
the textbox in which I want to do the addition is located) and not in the
subform where the "Yes/No" field and the "Total Cost" field is located.
Thank you for your help.
Michael

Allen Browne said:
Try a Control Source of:
=Sum(IIf([MyYesNo], [Total Cost], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael said:
I have a form named checks and a subform named expense. In the subform I
have a "Yes/No" field and a Total Cost Field. What I am trying to do is
to
have a text box in the form footer on the main form that will sum up the
Total Cost field whenever the "Yes/No" field is checked to Yes. I've
looked
around and couldn't find a similar question. Thank you for your help in
advance.
Click to expand...
Click to expand...
 
The idea is to put this into the subform. You can then carry the total back
to the main form to display it there.

1. Open your subform in design view.

2. If you don't see a Form Footer section at the bottom, click Form
Header/Footer on the View menu.

3. Put a text box in the form footer section, and give it these properties:
Control Source =Sum(IIf([exppaid], [Total Cost], 0))
Format Currency
Name txtSubTotal

4. Save the subform.

5. Open the main form in design view. Set the ControlSource of your text box
on the main form to:
=[NameOfYourSubformControlHere].Form.[txtSubTotal]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael said:
Mr. Browne
Thank you for your response.

I tried what you had suggested, here is my code
=Sum(IIf([exppaid],[Total Cost],0))
exppaid is the name of the "Yes/No" field.

However when I tested it out there was a "#Error" in the text box. I was
wondering if it is because this syntax is looking into the main form
(where
the textbox in which I want to do the addition is located) and not in the
subform where the "Yes/No" field and the "Total Cost" field is located.
Thank you for your help.
Michael

Allen Browne said:
Try a Control Source of:
=Sum(IIf([MyYesNo], [Total Cost], 0))


Michael said:
I have a form named checks and a subform named expense. In the subform
I
have a "Yes/No" field and a Total Cost Field. What I am trying to do
is
to
have a text box in the form footer on the main form that will sum up
the
Total Cost field whenever the "Yes/No" field is checked to Yes. I've
looked
around and couldn't find a similar question. Thank you for your help
in
advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top