Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 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
jedder18 is on a distinguished road
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.

jedder18 is offline   Reply With Quote
Old 08-07-2019, 06:08 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
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"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Tera (08-07-2019)
Old 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
jedder18 is on a distinguished road
Re: nested iif with Sum

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

jedder18 is offline   Reply With Quote
Old 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
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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
Pat Hartman is offline   Reply With Quote
Old 08-07-2019, 06:47 PM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
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"
arnelgp is offline   Reply With Quote
Old 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
jedder18 is on a distinguished road
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.

jedder18 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
NESTED iIF's Lanason Queries 8 04-03-2016 06:16 AM
Nested IIf diofree Modules & VBA 5 12-23-2015 07:54 PM
Converting nested Access queries into raw nested SQL queries ElaineBaker Code Repository 0 11-30-2014 11:49 PM
nested IIF jkncrew Queries 9 06-15-2007 07:28 PM
Nested If robart6 Forms 2 02-13-2004 09:37 AM




All times are GMT -8. The time now is 11:25 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


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World