Sum If Type Function in Access

[Bkper087]

I saw a number of topics that were related via the search, but am still a little lost. I am trying to create the equivalent of a SumIf function in Excel. I am creating a hiring and salary management database for a summer camp. I have a weekly salary field and a series of Yes/No check boxes that are related to the weeks staff members will be available. I want to write an expression that says if the week 1 field = "Y", and the week 2 field = "Y", but the week 3 field = "N" then multiply the salary by 2 based on the 2 weeks of availability. I have tried using DSum, but I am running into the parameter value error. I'm not sure why, it is probably unrelated. Anyway, I was just curious if that was the best option or if I should be using a different function.



Thanks!

[June7]

If they are truly Yes/No type fields then ="Y" or ="N" will fail. Yes/No field is Boolean type. Values are -1/0. Can use those or constants True/False without quote marks or not even use =. The syntax for IIf is:

IIf(expression, do this if expression true, do this if expression false)

So try:

Sum([Salary] * IIf(week1 AND week2 AND NOT week3, 2, 1))

or consider:

Sum([Salary] * IIf(week1,1,0) + IIf(week2,1,0) + IIf(week3,1,0))

[Bkper087]

Thanks for the reply. I wound up using something very similar. I used an IIF statement for each week and then added the those results in a separate column.