Home Forum Contact

 Access World Forums nested iif with Sum
 Register FAQ Members List Social Groups Top Posters Search Today's Posts

 08-07-2019, 05:43 AM #1 jedder18 Just Livin the Dream!   Join Date: Mar 2012 Posts: 135 Thanks: 31 Thanked 9 Times in 9 Posts nested iif with Sum =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.
 08-07-2019, 06:08 AM #2 arnelgp error reading drive A:     Join Date: May 2009 Location: somewhere out there Posts: 8,569 Thanks: 68 Thanked 2,745 Times in 2,630 Posts Re: nested iif with Sum 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)` __________________ "Never stop learning, because life never stops teaching"
 The Following User Says Thank You to arnelgp For This Useful Post: Tera (08-07-2019)
08-07-2019, 11:29 AM   #3
jedder18
Just Livin the Dream!

Join Date: Mar 2012
Posts: 135
Thanks: 31
Thanked 9 Times in 9 Posts
Re: nested iif with Sum

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

 08-07-2019, 06:22 PM #4 Pat Hartman Super Moderator   Join Date: Feb 2002 Location: Stratford,Ct USA Posts: 28,316 Thanks: 15 Thanked 1,601 Times in 1,520 Posts Re: nested iif with Sum 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) __________________ Bridge Players Still Know All the Tricks
 08-07-2019, 06:47 PM #5 arnelgp error reading drive A:     Join Date: May 2009 Location: somewhere out there Posts: 8,569 Thanks: 68 Thanked 2,745 Times in 2,630 Posts Re: nested iif with Sum 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. __________________ "Never stop learning, because life never stops teaching"
 08-08-2019, 03:34 AM #6 jedder18 Just Livin the Dream!   Join Date: Mar 2012 Posts: 135 Thanks: 31 Thanked 9 Times in 9 Posts Re: nested iif with Sum 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.

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post Lanason Queries 8 04-03-2016 06:16 AM diofree Modules & VBA 5 12-23-2015 07:54 PM ElaineBaker Code Repository 0 11-30-2014 11:49 PM jkncrew Queries 9 06-15-2007 07:28 PM robart6 Forms 2 02-13-2004 09:37 AM

All times are GMT -8. The time now is 01:07 AM.

 Microsoft Access Help General Tables Queries Forms Reports Macros Modules & VBA Theory & Practice Access FAQs Code Repository Sample Databases Video Tutorials Featured Forum post Sponsored Links