Formula paste error

[philipsw]

I am trying to paste this formula into one of my queries, but I get the "the text is too long to be edited" when I paste the formula into the designated field. Is there anyway to get this paste without getting the error?

Access experience level = medium.

CurrentPhaseMetrics: IIf([CurrentPhase]="Phase 1",IIf([NumDaysInPhase]>5,"RED",IIf([NumDaysInPhase]>3,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 2",IIf([NumDaysInPhase]>35,"RED",IIf([NumDaysInPhase]>25,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 3",IIf([NumDaysInPhase]>25,"RED",IIf([NumDaysInPhase]>20,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 3a",IIf([NumDaysInPhase]>5,"RED",IIf([NumDaysInPhase]>3,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 5",IIf([NumDaysInPhase]>5,"RED",IIf([NumDaysInPhase]>3,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 4",IIf([NumDaysInPhase]>3,"RED",IIf([NumDaysInPhase]>1,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 6",IIf([NumDaysInPhase]>5,"RED",IIf([NumDaysInPhase]>3,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 6a",IIf([NumDaysInPhase]>7,"RED",IIf([NumDaysInPhase]>5,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 6b",IIf([NumDaysInPhase]>90,"RED",IIf([NumDaysInPhase]>45,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 7",IIf([NumDaysInPhase]>2,"RED",IIf([NumDaysInPhase]>1,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 8",IIf([NumDaysInPhase]>30,"RED",IIf([NumDaysInPhase]>20,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phases Complete","Completed","Closed"))))))))))))))))))))

 

[spikepl]

This is waaaay to complex to assemble usiing IIF. Make a public function in a standard module and call it .

But - you should not hardcode such things at all . You should have a table where values are listed together with the corresponding colours.

 

[philipsw]

Thanks for the response Spikepl, however I have no clue as to what you're talking about or suggesting I do. Would you please give me that again like you're explaining it to a two year old...lol

 

[spikepl]

Here is how to create user-defined functions and use them in a query: http://datapigtechnologies.com/blog/index.php/creating-and-using-user-defined-functions-in-access/

 

[JANR]

This should get you started.

Function Phases(CurPhase As Variant, NumOfDays As Integer) As String
     Select Case CurPhase
        Case "Phase 1", "Phase 3a", "Phase 5", "Phase 6"
            If NumOfDays > 5 Then
                Phases = "RED"
                    ElseIf NumOfDays > 3 Then
                        Phases = "YELLOW"
                            Else
                                Phases = "GREEN"
             End If
        Case "Phase 2"
             If NumOfDays > 35 Then
                Phases = "RED"
                    ElseIf NumOfDays > 25 Then
                        Phases = "YELLOW"
                            Else
                                Phases = "GREEN"
             End If           
              ' .....Complete the rest of the cases yourself
       
        Case "Phases Complete"
            Phases = "Completed"
        Case Else
            Phases = "Closed"
    End Select
End Function

And you can call the function from your Query like so:

[FONT=Calibri]CurrentPhaseMetrics: Phases([CurrentPhase],[NumDaysInPhase])[/FONT]

Good Luck

JR