UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Expression Help - Iif Statement, Access 2016    
 
   
HJMLII
post Jan 22 2020, 10:22 AM
Post#1



Posts: 11
Joined: 22-March 19



Hello! I am trying to build an IIF statement that will return a value based on two different criteria...If the number returned is over 5.0, return 5.0...if the number is below 0, return 0. Here is what I have so far. The first part is working until I add the second criteria:

=IIf([AETdifference]*(-1)/10>5,5.0,[AETdifference]*(-1)) and ([AETdifference]*(-1)/10<0,0.0,[AETdifference]*(-1))

Any help would be greatly appreciated!
Go to the top of the page
 
cheekybuddha
post Jan 22 2020, 10:29 AM
Post#2


UtterAccess Moderator
Posts: 12,325
Joined: 6-December 03
From: Telegraph Hill


Try writing out your 2 parts separately first:

1. If the number returned is over 5.0, return 5.0
IIf(([AETdifference] * -1) / 10 > 5, 5.0, [AETdifference] * -1) / 10)

2 if the number is below 0, return 0
IIf(([AETdifference] * -1) / 10 < 0, 0.0, [AETdifference] * -1) / 10)

Then replace the 'ELSE' section of the first with the whole of the second expression:
CODE
=IIf(([AETdifference] * -1) / 10 > 5, 5.0, IIf(([AETdifference] * -1) / 10 < 0, 0.0, ([AETdifference] * -1) / 10))


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Jan 22 2020, 11:02 AM
Post#3


UA Moderator
Posts: 77,520
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

That's basically called "nesting."

CODE
IIf(-[AETdifference]/10<0,0,IIf(-[AETdifference]/10>5,5,-[AETdifference]))

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
HJMLII
post Jan 22 2020, 11:58 AM
Post#4



Posts: 11
Joined: 22-March 19



Thank you all, that worked perfectly!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 03:55 AM