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

#### galaxy

##### Registered User.
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
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
Where are you using this code?
Have you considered using Conditional Formatting.

#### Steve R.

##### Retired
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.
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","")))``

Replies
7
Views
294
Replies
4
Views
683
Replies
8
Views
754
Replies
12
Views
800
Replies
9
Views
551