nested iif with Sum (1 Viewer)

jedder18

Just Livin the Dream!
Local time
Today, 07:29
Joined
Mar 28, 2012
Messages
135
=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

..forever waiting... waiting for jellybean!
Local time
Today, 22:29
Joined
May 7, 2009
Messages
19,169
maybe, if not individual, what then?

your expression can be easily read as:
Code:
=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

Just Livin the Dream!
Local time
Today, 07:29
Joined
Mar 28, 2012
Messages
135
this should always be individual.
It's a separate formula from the group.
thanks for help
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:29
Joined
Feb 19, 2002
Messages
42,970
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

..forever waiting... waiting for jellybean!
Local time
Today, 22:29
Joined
May 7, 2009
Messages
19,169
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

Just Livin the Dream!
Local time
Today, 07:29
Joined
Mar 28, 2012
Messages
135
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.
 

Users who are viewing this thread

Top Bottom