nested iif with Sum

[jedder18]

=Sum(IIf([Date]<#7/1/2019#,IIf([GrpIndiv]="Individual",([Hours])*50.23,(IIf([GrpIndiv]="Individual",([Hours])*50.26)))))

THis only works for those meeting the 1st criteria...not if it's > 7/1

What am I missing?
I get no data at all for the 7/1 criteria.

 

[arnelgp]

maybe, if not individual, what then?

your expression can be easily read as:

=Sum(Switch([Date] < #7/1/2019# And [GrpIndiv] = "Individual", ([Hours]) * 50.23, [Date] >= #7/1/2019# And [GrpIndiv] = "Individual", ([Hours]) * 50.26), True, Null)

 

[jedder18]

this should always be individual.
It's a separate formula from the group.
thanks for help

 

[Pat Hartman]

There is nothing that says what to do for other dates. The expression is incomplete.

IIf(cond1, TruePath, FalsePath)

It is the second level of nesting that has confused the issue.

It might be simpler if you start with the "individual" condition. That way you won't have to repeat the date condition.

IIf(IIf([GrpIndiv]="Individual", IIf([Date]<#7/1/2019#, [Hours]*50.23, whatever you want to do for dates >= 7/1), whatever you want to do for non individual)

 

[arnelgp]

there is a portion there in the Switch that I posted:

…, True, 0)

meaning if it is not "Individual", regardless of date, then return 0.
replace 0 with your computation.

 

[jedder18]

Great idea to put the indiv 1st.
I'm getting so confused between Access and Crystal, I getted bogged down in too many details.
This is great...thanks so much.