Sum by Qtr in my Report

[Brian62]

I want to count how many (FindingTotal) I have done by quarter (dateofaudit) in my report.

I used this formula to get my numbers for the year: =Sum(IIf(Year([dateofaudit])=2011,[FindingTotal],Null))

Now I need to the same but break it down to quarters. How would I convert this to quarters. Thanks!

 

[vbaInet]

Check out the DatePart() function, it can return the quarter for a particular date:

DatePart("q", [DateField])

 

[Brian62]

I understand that formula, but I have to sum the FindingTotal with the quarter and by year as well.

This is the example I tried in a different script. =Sum(IIf(Year([dateofaudit])=2011,[FindingTotal],Null))

I thought something along this line would work.

 

[vbaInet]

DatePart() will replace Year()

 

[vbaInet]

If you want to add DatePart() (i.e. have Year() and DatePart()), you can use AND within the condition.

 

[Brian62]

How would I add it in with I need to get what I want? I have tried a few different ways and I can't get it to work.
This is what I tried: =Sum(DatePart("q",[dateofaudit]) and [FindingTotal],Null))
Thx

 

[Brian62]

I also tried this: =Sum(DatePart("q",[dateofaudit]=2011,[totalconsents],Null))
I need to pull by qtr (1st qtr, 2nd qtr, etc), by year and the "totalconsents" in those guidelines.

The error I am getting is: Data Type missmatch in criteria expression.

Year = DateofAudit and Totalconsents represents the total I did that date, i.e., 4/1/2011.. Confused....

 

[vbaInet]

=Sum(IIF(DatePart(...) = ?? AND Year(...) = 2011, [totalconsents], Null))

 

[Brian62]

=Sum(IIF(DatePart() = q AND Year([dateofaudit]) = 2011, [totalconsents], Null))
This is what I tried but I am getting an error for wrong arguement.

Not sure what to put in the ??. In the table I don't have it broken down by quarter, just the date entered that I am trying to use to get my qtrs I showed earlier. Appreciate your help but I am determined to get this.

 

[vbaInet]

You're getting there. Your DatePart() syntax is a bit off, have a look at post #3 again.

You have a group that breaks it down in quarters in your report don't you?

 

[Brian62]

No, I don't have it broken down by quarters. I am using a table for the graph I created. Everything works great but this issue. I already have the years totals but need it quarters for the Director. Everything is coming off the dateofaudit column.

Don't quite understand post 3, sorry...

 

[Brian62]

Here is what I use in my table:
dateofAudit------TotalConsents-----Totalfindings
1/1/2011-------------30---------------4-------
2/1/2011-------------59---------------16------
4/1/2011-------------15---------------0-------

Etc... hope this helps!

 

[Brian62]

I created a query breaking down the qtrs named Qtr. The output is 1 Qtr - 2011, 2 Qtr - 2011, etc... The rest of the columns remained the same. I tried
=Sum(IIF(DatePart(Qtr) = 1 Qtr - 2011 AND Year(DateofAudit) = 2011, [totalconsents], Null))
But I still can't get it to work at all. I know I am missing something...

 

[Brian62]

I figured out waht I wanted to accomplish for this part.
This is the code I used:
=Sum(IIf([Qtr]="1 Qtr - 2011",[TotalConsents],Null))
=Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null))

Now what I need is to get a percentage out of these.
When I try this code: =Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal])/[Totalconsents])

The error I get is: Division by zero

Have any ideas? Thx

 

[vbaInet]

I thought you wanted this? -->

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

If you already have a separate textbox that contains this:

=Sum(IIf([Qtr]="1 Qtr - 2011",[TotalConsents],Null))

and let's say the textbox is called txtSumTotContents, your code should then be:

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

To curb the "division by zero" problem, you can use something like this:

=IIF([txtSumTotContents] = 0, [COLOR=Red]Null[/COLOR], Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null)) / [txtSumTotContents]))

 

[Brian62]

Thanks for the codes. I modified the last one to fit. Here it is but I have a calculation problem.

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

My total consents are: 356
My total findings are: 32
-------------------------
The Total percent I should get is 11.125 but I am getting 10.6667. Don't understand why it is off.
I also like to round it off to the nearest whole number.

Thanks!!

 

[vbaInet]

Change the name of the Control. The field name and control should not be the same name if you're going to use it for this sort of calculation.

Then use the name of the control in the calculation as explained in my last post.

 

[Brian62]

I'm not exactly sure how I would do this. Can you write the code out for me? I've tried every way I can think of but no luck thus far. Much appreciated!

 

[vbaInet]

It is not code, it's a property. An example of a Control is a textbox. Look in the Property Sheet for that textbox and change the Name property (under the Other tab) to txtTotConsents (as mentioned), then refer to my post with the explanations.

 

[Brian62]

I changed the name of the text box name to: txtTotalConsents

The control sourse is: =IIf([txtTotalConsents]=0,Null,Sum(IIf([Qtr]="1 Qtr - 2011",[FindingTotal],Null))/[totalconsents])

The textbox shows #error. I'm not sure if I am doing it right but I know we are getting close. Thanks!