One more question, I'd like to make the new calcualted form field only add
records associated with the client currently selected on the form. As I
scroll through the clients, I'd like the DSUM to change accordingly. I'm
wondering if I should filter at the original query or the fields on the
form? Any thoughts?
"MX1" <mx*@mx1.abc> wrote in message
news:7m*******************@rwcrnsc52.ops.asp.att.n et...
Thanks Rick. I had never seen DSUM used in that context before. It
worked like a charm. You're awesome!
Thanks again!
"Rick Brandt" <RB*****@Hunter.Com> wrote in message
news:bj************@ID-98015.news.uni-berlin.de... "MX1" <mx*@mx1.abc> wrote in message
news:YV*********************@rwcrnsc51.ops.asp.att .net... I have a query written in MS Access that has a few calculated fields. Is it possible to refer to that query in a form field. I'd like the form field to show the sum of one of the columns from the query I've written. I've tried to put the following in the form field's control source property but
I'm getting an error. Query1 is the name of the query and CalculatedTotal is the calc'd field column I'm trying to get a total for in the form
field.
=Sum([Query1]!CalculatedTotal)
The standard aggregate functions only work on the current bound
RecordSet and the aggregation is based on where they are used (Group Header/Footer, Form Header/Footer, etc.). In your case Sum([CalculatedTotal) would work if the form was
actually bound to the query [Query1] and you placed the control in either the Form
Header or Form Footer.
If your form is bound to a different data source and you just want to display this sum on your form then you need to use a Domain Aggregate function as
these provide an argument for the DataSource to look at. In your case...
=DSum("[CalculatedTotal]", "[Query1]")
...should work. Domain Aggregate function have no concern about their placement either. You could place the control anywhere you like on your form and
it will return the same result.
