IIf statements with AND (multiple criteria)

[galaxy]

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.

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

 

[Steve R.]

As a quick (simpler) alternative approach, use the case statement.

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]

Where are you using this code?
Have you considered using Conditional Formatting.

 

[Steve R.]

Your post is not clear, but if you only have two states (red,green) a simple if statement will work.

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

 

[galaxy]

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.

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