setvalue and subform

[Guest]

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

You can sum on calculated field, if you have in the detail a field with
calculation such as
= [Field1] + [Field2]
If you want to sum on this field, instead of writing
=sum([FieldName])
You wold write
=Sum( [Field1] + [Field2])

 

[Guest]

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

 

[Guest]

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

If any of the values is null, it can cause an error, try
=Sum(nz([Budget],0)-nz([Paid],0))

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

One more thing, when you sum on this field, put the name of the fields in the
table and not the name of the fields in the form

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

StephWhitley,

Are Budget and Paid fields of the form's control source, or the name of
controls? The Sum function operates on fields only AFAIK.

Another approach is to create the calculated field in a query, and base your
form on the query:

[RemainingBudget]: [Budget] - [Paid]

Then you can sum the calculated field directly:

=Sum([RemainingBudget])

Hope that helps.
Sprinks

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

I tried that and got Error#. Does it matter if 'Paid' is a calculated field
(Domain Aggregate Function)?
--
Steph

One more thing, when you sum on this field, put the name of the fields in the
table and not the name of the fields in the form

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

:

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

Hi, Steph.

Yes, you must either repeat the calculation in the Sum statement or
calculate it in a query as described in my last post.

= Sum([Budget] - DSum(... yourexpression here...))

As I find this cludgy, I prefer to simply calculate it once in a query.

Hope that helps.
Sprinks

I tried that and got Error#. Does it matter if 'Paid' is a calculated field
(Domain Aggregate Function)?
--
Steph

One more thing, when you sum on this field, put the name of the fields in the
table and not the name of the fields in the form

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph


:

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

:

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

If the Paid field calculated in the record source of the form then you
shouldn't have any problem.

Give Sprinks suggestion a try, and try to sum on each field separately and
see what you get

I tried that and got Error#. Does it matter if 'Paid' is a calculated field
(Domain Aggregate Function)?
--
Steph

One more thing, when you sum on this field, put the name of the fields in the
table and not the name of the fields in the form

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph


:

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

:

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.

 

[Guest]

All of you had great suggestions, all of which I tried, except the last one
from Spinks which I couldn't try because one of the fields is not part of the
data source. Therefore it had to be a domain aggregate function. I tried
putting that in the calculation and got an error.

It's obvious calculating from that field isn't going to work which leads me
to my original question. Does anyone know which event to fire a setvalue
macro to a field bound to a table?
--
Steph

StephWhitley,

Are Budget and Paid fields of the form's control source, or the name of
controls? The Sum function operates on fields only AFAIK.

Another approach is to create the calculated field in a query, and base your
form on the query:

[RemainingBudget]: [Budget] - [Paid]

Then you can sum the calculated field directly:

=Sum([RemainingBudget])

Hope that helps.
Sprinks

Well both Ofer and Sprinks said the same thing so I tried:

=Sum([Budget]-[Paid])

and I got an error. Any other suggestions?
--
Steph

Steph,

You can sum a calculated field. For example, to sum a calculated field
whose Control Source is:

[Qty]*[UnitPrice] , place a textbox in the subform footer with Control
Source set to:

=Sum([Qty]*[UnitPrice])

Hope that helps.
Sprinks

:

Hi -- I have a subform on a form that has a calulated value. Since you can't
sum a calculated value at the bottom of the subform, I thought I would set
the value of the caluculated field to another blank field bound to a table.
Then I could make invisible the field bound to the table and sum it. Problem
is I don't know what event to use. Nothing works. I've tried domain
aggregate functions but that doesn't even work.