Sum a column of values from a query in a form field

[MX1]

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)

Many Thanks.

[MX1]

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.

[MX1]

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.

[MX1]

All set, I filtered by ClientID on the dsum in the form and it worked. The
problem was that I didn't have ClientID selected as a field in the original
query. Thanks again!
"MX1" <mx*@mx1.abc> wrote in message
news:lB*********************@rwcrnsc51.ops.asp.att .net...

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.