Sum field 1 if field 2 equals a value

[arothacker]

I have a form called "ViewHeadcount" that is linked to the table "Updated Headcount"



On the form there are fields "Employee Type" and "Count". There is a textbox at the top that I want to sum the values in "Count" for all the records that "Employee Type" = Salary.

I tried putting =Sum(IIf([Employee Type]="Salary",[Count],0)) but I get #Error. Any ideas??

Thanks!

[Bob Fitz]

Not sure if this would work, but perhaps something like:
=DSum("[Count]", "[YourTableOrQueryName]", "[Employee Type] = Salary")
You would need to replace [YourTableOrQueryName] with the domain (table or query) used for the form.

[arothacker]

I tried putting =DSum([Count],[qry_ViewHeadcount],[Employee Type]="Salary") and I get a #Name? error now

[Bob Fitz]

Try:
=DSum("[Count]","[qry_ViewHeadcount],"[Employee Type]=Salary")

[arothacker]

It says it has invalid syntax. Maybe it's how everything is connected....

I have qry_viewheadcount that the user is prompted to select values to filter. Once they select the values the records are filtered and displayed on the form "Headcount". At the top of this form I have a textbox that I want to sum the "Count" field whenever the "Employee Type" field = Salary. Also the Employee Type field is a combobox...would that have anything to do with it?

[Bob Fitz]

Type field is a combobox...would that have anything to do with it?

I think it may.
What is the SQL of the combo box.
What are it Column widths and Column Count property settings.

[June7]

Combobox could be issue. Is this because of lookup set in table? I NEVER set lookups in table, especially if there is an alias. Possibly the actual value of the field is not "Salary" but a numeric ID. Change the control to a textbox. What value displays?

Did you put the textbox with Sum expression in a header or footer section? That expression should work with proper criteria.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved.

[arothacker]

The field is called Employee Type and is a drop down with the choices of "Salary", "Wage", and "Other". We're trying to display at in the header the sum of the "Count" field for Salary, Wage, and Other. So we would have 3 textboxes at the top with 3 different sum values.

I unfortunately can't provide the database because it has confidential information in it.

[June7]

Bob forgot apostrophe delimiters.

=DSum("[Count]","[qry_ViewHeadcount],"[Employee Type]='Salary'")

However, try SQL aggregate instead of domain aggregate:

=Sum(IIf([Employee Type]="Salary",[Count],0))

[arothacker]

Hm that's bizarre. I used that equation before (look at first post) and it was giving me an error but now it seems to be working! Not sure why haha but thanks everyone!

[June7]

Count is a reserved word. Should avoid reserved words as field names.