Sum by Qtr in my Report

[Brian62]

I'm trying to follow your suggestions but so far I still can't get this darn thing to work. It's driving me crazy.

 

[vbaInet]

You changed it all?

=IIf([txtTotalConsents]=0,Null,Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null))/[totalconsents])

 

[Brian62]

Just a little after I tried what you suggested. I tried the code you gave me but I am still getting this erorr when I populate the report: #error

 

[vbaInet]

You've changed the bit in red and it still errors?

 

[Brian62]

Yes. It doesn't like the * at all. When I remove them it still gives me #error.

 

[vbaInet]

Yes. It doesn't like the * at all.

What *?

 

[Brian62]

Originally you had the * symbol in your code and got an error so I removed it then tried it without them. I went back to your last post then it was removed. That's when I still get the #error. So far everything that has been tried either gives me the wrong calculation or has an error. Confused as well!

 

[vbaInet]

I've just gone through my replies and I definately didn't put an asterisk in any code (or within a sentence)

What code have you got now?

 

[Brian62]

This is part of the email I recieved.

You changed it all?

=IIf([txtTotalConsents]=0,Null,Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null))/[*totalconsents*])


Then you made the change which now is in RED.

 

[vbaInet]

Do you see asterisk? That's strange because I didn't put it there:

=IIf([txtTotalConsents]=0,Null,Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null))/[txtTotalconsents])

 

[Brian62]

Well my email doesn't lie... It could of been by accident or some ghost....

Ok, here is the rest of the email:

Dear Brian62,

vbaInet has just replied to a thread you have subscribed to entitled - Sum by Qtr in my Report - in the Reports forum of Access World Forums.

This thread is located at:
http://www.access-programmers.co.uk/forums/showthread.php?t=207086&goto=newpost

Here is the message that has just been posted:
***************
You changed it all?

=IIf([txtTotalConsents]=0,Null,Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null))/[*totalconsents*])
***************


There may also be other replies, but you will not receive any more notifications until you visit the forum again.

All the best,
Access World Forums

 

[Brian62]

Still not working.
textbox Name: txtTotalConsents
Code:
=IIf([TotalConsents]=0,Null,Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null))/[Totalconsents])

Still getting an eronious number: 10.6667 when it should be 11.125

 

[vbaInet]

I gave you the amended code in post #30.

Code isn't properly formatted in an e-mail which is why you should read all your replies directly on the forum. The e-mail you get is automated.

 

[Brian62]

I tried that code but it won't work because it doesn't have "totalconsents" in it. When I supplement "Txttotalconsents" (at the end or both ends) with "totalconsents" I get the incorrect percentage.

Do you want me to send you the part of the database that we are having the problem solving?

 

[vbaInet]

It has to be substituted on both sides whether it works or not, that's how it should be written.

Copy and paste the data of the 2 fields into a spreadsheet, tell me what you expect to be the percentage and I will see where you're going wrong. Show us a screenshot of your report in design view too.

Also remember that to get a percentage the result needs to be multiplied by 100.

 

[Brian62]

Here you go. Won't accept excel file so I had to use word. I hope this helps. Thanks Again!!!

 

[Brian62]

I also attached the query results so you know how it is pulled to the report. Thanks!

 

[vbaInet]

You do realise that the percentage of 32 in 356 is 8.99% and not 11.125%?

(32 / 356) * 100 = 8.988

What you calculated is:

356 / 32 = 11.125 <-- this isn't a percentage.

Also are the numbers 356 and 32 showing in their individual textboxes correctly?

 

[Brian62]

Yes they are correct. I went back checked the table.

 

[vbaInet]

Just like you did with txtTotalConsents, do the same for the other field and change the code to become the textbox name.

Did you set the Format property of the textbox to Percentage?