Calculating Subform Field on Main Form

[jvhazelbaker]

Main form name = Review and Edit Staffing on a Project

Subform name = Priority Data Table Subform1

Field to sum = Your Priority Number

The field to sum is on the subform formatted as a datasheet. I have placed a textbox on the main form and below is the formula I have used that is resulting in an error. Am I doing something wrong, or can this just not be done?

=Sum(Forms![review and edit staffing on a project]![Priority Data Table subform1].Form![Your Priority Number])

 

[MA04]

Hi this link might help,

http://www.mvps.org/access/forms/frm0031.htm

HTH,
M-.

 

[jvhazelbaker]

I tried changing the formula based on the link you sent, but it didn't work. I took out the SUM function and had just:

=Forms![review and edit staffing on a project]![Priority Data Table subform1].Form![Your Priority Number])

to see if my syntax was correct. It is as it returned an actual number. It is only when a function is added that it produces an error.

When the formula is typed like this:

=Forms![review and edit staffing on a project]![Priority Data Table subform1].Form![Your Priority Number].sum)

it returns "#NAME?" rather than error. Why is that incorrect?

 

[MA04]

Hi try placing this on the control source of your textbox:

=[Priority Data Table subform1]![Your Priority Number]

HTH,

M-.

 

[jvhazelbaker]

When I put =[Priority Data Table subform1]![Your Priority Number], it returns the first number in the datasheet.

So I put =Sum([Priority Data Table subform1]![Your Priority Number]) Error is returned.

I put =[Priority Data Table subform1]![Your Priority Number].sum, #NAME? is returned.

Why does adding a function create a problem when the code is obviously correct?

 

[MA04]

Hi,

This could be a solution, create a textbox in the subform footer call it say: total. In its control source enter
=Sum([Your Priority Number]) then save. Now go to your main form and in the textbox you created enter in the control source =[Priority Data Table subform1]![total]. Total is the textbox you created in the footer of your subform, that should work.

HTH,
M-.

 

[jvhazelbaker]

Works great!!! Thank you so much!!!

 

[jvhazelbaker]

So, another problem regarding this formula, which works great as it currently is, but an issue as come up. The form includes the following fields:

Your Name
Role
Your Priority Number

Which in form view could look like this:

Your Name Role Your Priority Number
Jaime Team Leader 21
Angela FYI 99
Theresa Sponsor 13

When "FYI" is the selected role, I want that number to be excluded from the sum. So rather than the total being 133, the formula would produce 34. I have tried the following with no success:

=iif([Role]="fyi",[your priority number]=0,sum([your priority number]))
and
=iif([Role]="fyi",[your priority number] is null,sum([your priority number]))

Any suggestions?