totaling an unobund calculated control in form header

[Sandy]

I know this should be simple but for some reason I am just not getting this.

I have a text box control on a continuous form as follows:

Name: lotCost
Control Source=IIf([qUnit]="each",[qPrice]*[qQty]*(Forms![Main
form].BuildQty),IIf([qUnit]="LOT",[qPrice]*[qQty]))

The control calculates perfectly in each row of the form.

In the Form Header I want to total the lotCost field.

In the header of the form, I have tried an unbound text box with
control source = sum([lotCost]) but it give me an #error.

Each time I play wiht the formula (eg using =nz([lotCost]) just to get
something to show up ) I get a message "database can not find the field
"forms" refered to in your expression" but then the field calculates
correctly.

I am stumped!

Any help would be greatly appreciated!
sandy

 

[Graham Mandeno]

Hi Sandy

For continuous forms, I usually find it more reliable to put such
calculations in a field of the RecordSource query, rather than in a control.

I see your expression refers to Forms![Main form].BuildQty. I infer from
this that your continuous form in question is a subform of [Main form] and
that the table containing qUnit, qPrice and qQty is related to a parent
table containing BuildQty. Is this correct?

If so, I suggest you add the parent table and BuildQty to your subform's
recordsource query, and add a calculated field:
lotCost: [qPrice] * [qQty] * IIf( [qUnit]="each", [BuildQty], 1 )

Then lotCost should behave itself just like the other bound fields.

 

[Sandy]

Awesome! thanks so much

Hi Sandy

For continuous forms, I usually find it more reliable to put such
calculations in a field of the RecordSource query, rather than in a control.

I see your expression refers to Forms![Main form].BuildQty. I infer from
this that your continuous form in question is a subform of [Main form] and
that the table containing qUnit, qPrice and qQty is related to a parent
table containing BuildQty. Is this correct?

If so, I suggest you add the parent table and BuildQty to your subform's
recordsource query, and add a calculated field:
lotCost: [qPrice] * [qQty] * IIf( [qUnit]="each", [BuildQty], 1 )

Then lotCost should behave itself just like the other bound fields.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I know this should be simple but for some reason I am just not getting
this.

I have a text box control on a continuous form as follows:

Name: lotCost
Control Source=IIf([qUnit]="each",[qPrice]*[qQty]*(Forms![Main
form].BuildQty),IIf([qUnit]="LOT",[qPrice]*[qQty]))

The control calculates perfectly in each row of the form.

In the Form Header I want to total the lotCost field.

In the header of the form, I have tried an unbound text box with
control source = sum([lotCost]) but it give me an #error.

Each time I play wiht the formula (eg using =nz([lotCost]) just to get
something to show up ) I get a message "database can not find the field
"forms" refered to in your expression" but then the field calculates
correctly.

I am stumped!

Any help would be greatly appreciated!
sandy

 

[Graham Mandeno]

You're welcome, Sandy! Thanks for the feedback.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Awesome! thanks so much

Hi Sandy

For continuous forms, I usually find it more reliable to put such
calculations in a field of the RecordSource query, rather than in a
control.

I see your expression refers to Forms![Main form].BuildQty. I infer from
this that your continuous form in question is a subform of [Main form]
and
that the table containing qUnit, qPrice and qQty is related to a parent
table containing BuildQty. Is this correct?

If so, I suggest you add the parent table and BuildQty to your subform's
recordsource query, and add a calculated field:
lotCost: [qPrice] * [qQty] * IIf( [qUnit]="each", [BuildQty], 1 )

Then lotCost should behave itself just like the other bound fields.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I know this should be simple but for some reason I am just not getting
this.

I have a text box control on a continuous form as follows:

Name: lotCost
Control Source=IIf([qUnit]="each",[qPrice]*[qQty]*(Forms![Main
form].BuildQty),IIf([qUnit]="LOT",[qPrice]*[qQty]))

The control calculates perfectly in each row of the form.

In the Form Header I want to total the lotCost field.

In the header of the form, I have tried an unbound text box with
control source = sum([lotCost]) but it give me an #error.

Each time I play wiht the formula (eg using =nz([lotCost]) just to get
something to show up ) I get a message "database can not find the field
"forms" refered to in your expression" but then the field calculates
correctly.

I am stumped!

Any help would be greatly appreciated!
sandy

 

[Paul Shapiro]

I don't think Access will calculate totals on a calculated textbox. Repeat
the original expression in the totals textbox control source:
Control Source=Sum(IIf([qUnit]="each",[qPrice]*[qQty]*(Forms![Main
form].BuildQty),IIf([qUnit]="LOT",[qPrice]*[qQty])))