Totals Calculations

[kentwood]

On report, formated Check Boxes That only show a yes, are shown below and work just fine. Have 2 text boxs on form for Dollars and Dollars2. The user will pick q check box for Prof or Fac and show a $ amount in “Dollars”. They will then pick a check box from Prof2 or Fac2 and show a $ amount in “Dollars2”. The Dollars appear correctly on the form and report. It’s the totals that are way off on the report. In looking through the forum, I tried several suggestions, but to no avail. I have shown what I have on my report currently, and what the result is. I have then shown what the result should be. Way off. Your help would be appreciated.

CHECK BOXES
=Iif([Prof],”Yes”,””)

=Iif([Fac],”Yes”,””)

=Iif([Prof2],”Yes”,””)

=Iif([Fac2],”Yes”,””)
DATE FOOTER

Mo End Prof =Sum(IIf([Prof]+[Prof2]=Yes,[Dollars] +[Dollars2],0)) 10,381.30
Mo End Fac =Sum(IIf([Fac]+[Fac2]=Yes,[Dollars] + [Dollars2),0)) 6,620.30
Mo End Total =Sum([Dollars]+[Dollars2]) 10,381.30

REPORT FOOTER

YTD Fac =Sum(IIf([Prof]+[Prof2]=Yes,[Dollars] +[Dollars2],0)) 15,160.85
YTD Prof =Sum(IIf([Fac]+[Fac2]=Yes,[Dollars] + [Dollars2),0)) 6,620.30
YTD Total =Sum([Dollars]+[Dollars2]) 10,381.30

ACTUAL SHOULD BE:

Prof – 13,352.19
Fac - 8,159.97
Tot - 21,512.16

 

[suepowell]

Hi

I think your logic in your iif statements might be wrong.

try the following:

Mo End Prof =Sum(IIf(([Prof]=yes),[dollars],0)+iif(([Prof2]=Yes),[Dollars2],0))

or possibly

Mo End Prof =Sum(IIf(([Prof]=yes),[dollars],0))+sum(iif(([Prof2]=Yes),[Dollars2],0))

It depends on exactly what your reprto structure is.

If I have got completely the wrong end of the stick, post some more details and I will try again

Sue

 

[kentwood]

Acually you are on the right track, but using either of your suggestions gives me error messages regarding the use of (( or commas. My DB is too big to send out in a zip, but I am sending you a copy of my form, as well as the report output as it is right now. As you look at the form/report, the fields Reason and Service are Combo Boxes, which as you look at the report, you can tell are not working right either. I used the serv code table and reason code table to set up the boxes, but linked them to the fields RsnCd & RsnCd2, Tos and Tos2. (1) If you know how to make those show up correctly on the report, I would be indebted to you. (2) The options you presented for totals I believe I tried yesterday as well. Hope this helps some as my office would like this tomorrow.

 

[suepowell]

Hi,
Ive had a look at your forms and reports, but I can't see anythink obvoiusly wrong.

If your database is too big to zip could you just export the form and report, and the tables that feed them to a dummy database and zip that.
If you don't want to export sensitive data, just export the table structure and put a couple of records with representative data into them.

I'm afraid I can't help any more at the moment, cos I just can't see what the problem might be.

Sue

 

[SforSoftware]

Use this from Sue:
Mo End Prof =Sum(IIf(([Prof]=yes),[dollars],0))+sum(iif(([Prof2]=Yes),[Dollars2],0))

But replace , with ; in an expression

Bert

 

[kentwood]

OK Sue, Did what you said and I was able to get it in a zip file. Again, please note the output on my report based on the combo boxes (I don't know how to fix that) and of course the totals problem. If there is an answer to this, I know that I will have learned some valuable information as a result. Thanks for your help on this.

 

[suepowell]

Hi,

I have downloaded your database and think I have solved your problems.

I attach the amended database for you to see.

Basically I put the expression

=Sum(IIf(([Professional]=Yes),[dollars],0))+Sum(IIf(([Professional2]=Yes),[Dollars2],0))

in the text box for professional payments

=Sum(IIf(([Facility]=Yes),[dollars],0))+Sum(IIf(([Facility2]=Yes),[Dollars2],0))

in the box for facillity payments

and

=Sum([Dollars])+Sum([Dollars2])

in the totals box.

The same expressions go in the group footers and report footers, and seem to work correctly in my version.

To get your combo boxes to show correctly, I have changed them to combo boxes on the report, and linked them to the tables showing the reasons, as you did on the form. They don't appear to be combo boxes on the printed report.

As a point to remember, If you set up the field in the table as a combo box in the first place (using the linked tab for the field), the field will always go onto any forms or reports as a combo box so the problem does not arrise .

Hope this helps

Sue.

 

[kentwood]

Sue,

The result is absolutely perfect!! I have been stressing over this thing for at least 4 days, trying everything I knew. What I did not realize, was that I could set up a combo box on my report as you did. I will keep your "point to remember" handy for future projects.

I am not sure I will ever figure out the sequence of the combination of IIF and Sum statements, where you put the commas, brackets, parenthesis and use of the ,0 or ,2 etc. It is so very confusing, yet each time I do something like this, I learn so much more than I can in a book.

I only have one question. On my previous report, where text boxes (not combo) appeared, I was able to indicate on my properties to "grow" or "shrink". Since I have a few entries that are a bit long, short of stretching the combo box vertical or horizonally, is there a property that I am missing? If not, I will just make things a bit shorter.

Again, thanks so much for the help and excellent learning experience.