IIf statements with AND (multiple criteria) (1 Viewer)

galaxy

Registered User.
Local time
Today, 15:15
Joined
Feb 26, 2013
Messages
16
I have a list of projects and I need to display their status (Red or Green) in a text box.

My fields are [PercentComplete],[StartDate],[EndDate],[ReportDate]

There are two ways a project could have a red status.

1. [PercentComplete] < 100 AND [ReportDate] < [EndDate]

OR

2. [PercentComplete]<100 AND IsNull([EndDate])


There is one way it could have a green status:
[PercentComplete] = 100 AND Not IsNull([StartDate])

This is the best I could come up with for the Iif statement, but I get "invalid argument" which I suspect relates to the AND portion. I appreciate any help.

Code:
Iif([PercentComplete] < 100 AND [ReportDate] < [EndDate], "Red",
[PercentComplete]<100 AND IsNull([EndDate]),"Red",
[PercentComplete] = 100 AND Not IsNull([StartDate]),"Green","")))
 

Steve R.

Retired
Local time
Today, 16:15
Joined
Jul 5, 2006
Messages
4,710
As a quick (simpler) alternative approach, use the case statement.

Code:
if  [PercentComplete] < 100 AND [ReportDate] < [EndDate] then intNumber =1

if PercentComplete]<100 AND IsNull([EndDate]) then intNumber =1

if [PercentComplete] = 100 AND Not IsNull([StartDate]) then intNumber =2

Select Case intNumber
     Case 1
           Color is red
     Case 2
           Color is green
End select
 

bob fitz

AWF VIP
Local time
Today, 21:15
Joined
May 23, 2011
Messages
4,726
Where are you using this code?
Have you considered using Conditional Formatting.
 

Steve R.

Retired
Local time
Today, 16:15
Joined
Jul 5, 2006
Messages
4,710
Your post is not clear, but if you only have two states (red,green) a simple if statement will work.

Code:
Set color to red
if [PercentComplete] = 100 AND Not IsNull([StartDate]) then set color to green
 

galaxy

Registered User.
Local time
Today, 15:15
Joined
Feb 26, 2013
Messages
16
Turned out that a nested IIf statement works just fine with "AND". There are three states (Red, Green, Blank) so Steve's example would not work.

Code:
IIf([PercentComplete] < 100 AND [ReportDate] < [EndDate]), "Red",IIf ([PercentComplete]< 100 AND IsNull([EndDate]),"Red", iif([PercentComplete] = 100 AND Not IsNull([StartDate]),"Green","")))
 

Users who are viewing this thread

Top Bottom