Summing values based on specific criteria in a field

[Sandy]

In the Form Header for my subform, I am trying to add up the values based on
the value in another field in that sub form record.

Specifically, I am trying to Sum the number of days [NumDaysTaken] for an
Emp_ID, for all records that have "Vacation Day" in the LeaveType field.
LeaveType is a lookup so the value is a number.

=Sum([NumDaysTaken] ) where [Type] =1 And [EmpID] =
Forms![f_MainEmployeeData]![Emp_ID] )

I am getting an Invalid syntax error --- I have tried all combos of brackets
but am missing something.

thanks
sandy

 

[Sandy]

I figured out a solution by adding this to the forms query:

NumVacDays: IIf([type]="2",[numdaysoff],0)

thanks!

 

[John Vinson]

In the Form Header for my subform, I am trying to add up the values based on
the value in another field in that sub form record.

Specifically, I am trying to Sum the number of days [NumDaysTaken] for an
Emp_ID, for all records that have "Vacation Day" in the LeaveType field.
LeaveType is a lookup so the value is a number.

=Sum([NumDaysTaken] ) where [Type] =1 And [EmpID] =
Forms![f_MainEmployeeData]![Emp_ID] )

I am getting an Invalid syntax error --- I have tried all combos of brackets
but am missing something.

thanks
sandy

You're mixing languages here. What you have would be (sort of) valid
SQL in a Query, but that's not the language used in Control Sources.

I'd suggest putting a calculated field in the Query upon which the
subform is based:

CountDays: IIF([Type] = 1, [NumDaysTaken], 0)

Assuming that the Subform/s Master/Child Link Field is Emp_ID, you can
simply put

=Sum([CountDays])

in the COntrol Source of a subform footer textbox to get the desired
sum.

John W. Vinson[MVP]