The project I'm currently working on has a Statistics sheet which displays, among other things, the total dollar figure of Closed cases.

Two separate amounts, Estimated and Actual, are added up and displayed in the corresponding text box named "ClosedTotal11". These amounts are calculated using the DSum method in their respective hidden text boxes, "ClosedActualTotal" and "ClosedEstTotal". Within the queries used for the DSum method, the current year is extracted and named as "ThisYear".

Since this is an ongoing statistic needing to be displayed each year, I want to avoid having to duplicate my work. I'd like to be able to specify the criteria, in this case for ClosedTotal11 by using ThisYear=2011 in the Control Source.

And for 2012, create another textbox called ClosedTotal12, use ClosedActualTotal and ClosedEstTotal, extract the data for 2012 by using ThisYear=2012 and display it in ClosedTotal12.

Is something like this possible?

Kevin

couldnt you use closedtotalthisyear instead of closedtotal11 or 12 ?

yes, in you can have "where" clause in the recordsource or you can use a filter.

Hi Bob, thanks for looking at this!

I probably could use "closedtotalthisyear" instead of the other but the Statistics page has totals going back to 2008; I'm just naming them this way to keep everything consistent and identifiable.

As for using Where in the Control Source, how would I do that when I'm adding the results "=Nz([ClosedEstTotal])+Nz([ClosedActualTotal])" from the other two hidden text boxes for "ClosedActualTotal" and "ClosedEstTotal"?

This is ClosedEstTotal

MetricsClosedGuarEstAll


and this is ClosedActualTotal

MetricsClosedActualDollars



Kevin

is that field also in the table casprimary ?

No. ThisYear is a formatted field using OpenDate, which is a field in a table named Details.

unless i am misreading you should be able to use your where statement with an AND clause. something like this

WHERE Details.CaseStatus)="Closed" and details.thisyear = "12"

Unfortunately, it is a little more complicated that that.

The two text boxes, ClosedActualTotal and ClosedEstTotal, which contain DSum calculations, are mathematically added together in the ClosedTotal11 text box as =Nz([ClosedEstTotal])+Nz([ClosedActualTotal])

What I need is a way to set the criteria for this result to be specific, i.e. 2011 or 2012, as the case may be.

I'll probably end up just creating additionally queries for the years in question. I don't believe this particular report is going to be used past 2014 so it won't involve any additional updating.

so you only want to do a dsum to those fields if the underlying data is in the specific year ??

Bob, sorry for taking so long to get back to you.

I've pretty much given up on the idea of having the queries do all the calculations prior to displaying the information. Due to the differences in the sources of the data and how they are queried, I'm going to have to stick with my original plan of calculating the data on the report via the two text boxes, in other words, mathematically add the two hidden text boxes together and display the result in another text box. I know this will slow down the report in loading but I can't find any other way to do it.

Thanks again for your help.

Kevin