And I'm a little confused also.
Is this dsum formula in a control in the detail section of your subform? Or
is there some other formula in a control in the detail section of your
subform (in your original post, you state "The detail section contains
fields that are doing calculations."), and you are wanting the sum of those
calculated fields in your subform's footer? Are you wanting to sum the dsum
expressions in your subform's footer?
Looking back at your original post again, I also find this confusing:
<quote>
There is a text box that has source info going into Hours on a table.
The data on that field has a formula:
=DSum("[Hours]","Labor Metrics","[Tech manual ID] =Forms![Labor Metrics
(Project)]![Labor Metrics (Project) subform]![Tech Manual ID]")
<endquote>
Textboxes do not have "source info going into" a field in a table; if they
are bound to a field in a table (or query), they display - and usually also
allow entry or editing of - data in that field. But a textbox cannot be
both bound to a field in a table and contain a calculated value (eg, an
=dsum(...) expression.
And now some further points, leading to some possible answers:
Since you are only concerned with controls in the subform itself (in the
detail section and footer), you do not need any of the Forms!... constructs;
you do need the [Tech Manual ID] field to be available in the subform. I'm
assuming, also, that when you say in your last post "In addition it is a
number", that you are referring to [Tech Manual ID] (rather than the Sum).
If you want the dsum expression in an unbound textbox in the detail section,
use:
=DSum("[Hours]","Labor Metrics","[Tech manual ID] = " & [Tech Manual
ID])
This will give the sum of the Hours field from the Labor Metrics table, for
all records where [Tech Manual ID] is equal to the [Tech Manual ID] of the
record in that row of the detail section.
If you put this same expression in a textbox in the subform's footer, it
will display the sum of the Hours field from the Labor Metrics table, for
all records where [Tech Manual ID] is equal to the [Tech Manual ID] of the
currently selected row in the detail section - as you move between rows with
different [Tech Manual ID] values, the figure displayed in this textbox will
change. As an aside, I can't think of any sensible reason to do such a
thing ;-)
If you want to sum the dsum expression (in an unbound textbox in the detail
section) in the footer, you will need to enter the following expression in a
textbox in the footer:
=Sum(DSum("[Hours]","Labor Metrics","[Tech manual ID] = " & [Tech Manual
ID]))
You need this because, although you can enter the expression
=Sum([FieldName])
in a form's (or subform's) footer (an answer suggested by another responder
to your initial post), you cannot enter the expression
=Sum([ControlName])
where [ControlName] is the name of the control containing a calculated
expression; to sum calculated expressions, you must use
=Sum(Calculated Expression)
Again, HTH. If you still can't solve it, please post all details of exactly
what you're trying to do, and what fields and calculated expressions you
have in your subform's detail and footer sections.
Rob
bzeyger said:
I am still a little confused on what the entire expression should read.
There is a sub form and the formula occur in the detail section of the
subform. The Sum would be in the footer of subform. In addition it is a
number.
Labor Metrics (Project) subform: is the name of the subform
Labor Metrics (Project): is the name of the main form
I sorry to be such a pain, I just can't figure it out, thanks for your
time.
Again the detail
Rob Parker said:
Well, there's a couple of things wrong with that formula, but I'm not
sure
exactly what it should be, since the criteria expression contains a name
which includes "subform", so I'm assuming that there's a subform involved
somewhere, which you haven't mentioned.
The first problem is that your criteria expression is simply a string, so
it's not even trying to use the [Tech Manual ID] value which it's
supposed
to be getting from somewhere. You need to write that bit as:
...,"","[Tech Manual ID] = " & Forms![Labor Metrics (Project)]![Labor
Metrics (Project) subform]![Tech Manual ID])
if [Tech Manual ID] is a number, or as:
...,"","[Tech Manual ID] = '" & Forms![Labor Metrics
(Project)]![Labor
Metrics (Project) subform]![Tech Manual ID] & "'")
if it's a text string. For clarity, that is:
...,"","[Tech Manual ID] = ' " & Forms![Labor Metrics
(Project)]![Labor
Metrics (Project) subform]![Tech Manual ID] & " ' ")
And, if it's a string which may contain an apostrophe, you should
substitute
each single-quote character with two double-quote characters.
The second problem is that your reference to a field/control on a subform
is
wrong. And, as I said earlier, you haven't mentioned any subform. So
here's some alternatives.
1. If the dsum expression is in the footer of the form containing the
[Tech
Manual ID] field/control (either the main form or the subform), all you
need
is (for the numeric value)
...,"","[Tech Manual ID] = " & [Tech Manual ID])
2. If the dsum expression is in the footer of the main form, and [Tech
Manual ID] is the link field between the main form and a subform, the
same
expression as in alternative 1 will work.
3. If the dsum expression is in the footer of the main form, and [Tech
Manual ID] is a non-linked field on the subform, you will need (for the
numeric value)
...,"","[Tech Manual ID] = " & [Forms]![Labor Metrics
(Project)]![Labor
Metrics (Project) subform].[Form]![Tech Manual ID])
The third term here must be the name of the subform control on the main
form, not the name of the subform form (if they are different). And the
separator between the [SubformControlName] and the following [Form] term
must be a dot (.), rather than a bang (!). Within the reference to the
field, all the other sepaators can be either a dot or a bang; my normal
preference is to use dots for all, so I would write
...,"","[Tech Manual ID] = " & [Forms].[Labor Metrics
(Project)].[Labor
Metrics (Project) subform].[Form].[Tech Manual ID])
HTH,
Rob
I was having trouble with a specific formula. Assistance would be
greatly
appreciated.
I have a form that contains a form header, detail, and form footer.
The detail section contains fields that are doing calculations. There
is a
text box that has source info going into Hours on a table.
The data on that field has a formula:
=DSum("[Hours]","Labor Metrics","[Tech manual ID] =Forms![Labor Metrics
(Project)]![Labor Metrics (Project) subform]![Tech Manual ID]")
When the form is run, it brings up information for all the directed
results.
I am trying to get the total to calculate in the footer. What formula
should
I use?
Thanks for the time.
