How to display data. (Criteria and organization)

[offie]

So there might be another way to go about this, but I thougt to use IIF()

I have a field that contains either S (scale), R (range), and Y (Yes/No)
In another field there are numbers from 0 to 5

However if the 1st field is S then:
0=N/A 1=1 2=2 3=3 4=4 5=5

If it's R:
0=Poor 1=Fair 2=Average 3=Good 4=Exellent

If it's Y:
0=No 1=Yes


I want to display this data somehow so I figured that I would first convert anything that needs to be changed and then figure out how to sort it

So I came up with:

(
IIF([EvaluationAllInfo].[PerformanceType]="S", IIF([EvaluationAllInfo].[PerformanceResult]=0,"N/A", [EvaluationAllInfo].[PerformanceResult]))
OR IIF (EvaluationAllInfo].[PerformanceType]="R",
(
IIF([EvaluationAllInfo].[PerformanceResult]=0,"Poor")
OR IIF([EvaluationAllInfo].[PerformanceResult]=1,"Fair")


OR IIF([EvaluationAllInfo].[PerformanceResult]=2,"Average")
OR IIF([EvaluationAllInfo].[PerformanceResult]=3,"Good")
OR IIF([EvaluationAllInfo].[PerformanceResult]=4,"Exellent ")
)
)
OR IIF([EvaluationAllInfo].[PerformanceType]="Y", IIF([EvaluationAllInfo].[PerformanceResult]=0, "No", "Yes"))
)

This doesn't work, and I get -1 for all values. Is my format wrong, or is this just not a valid format?

Ideas/Suggestions?
Thanks!

[June7]

Your syntax has errors. Do not use those ORs (need commas) and the parens are not right. You might want to review how to structure nested IIfs.

However, this may be simpler:

Switch([PerformanceType]="S",IIf([PerformanceResult]=0,"N/A",[PerformanceResult]), [PerformanceType]="R",Choose([PerformanceResult],"Poor","Fair","Average","Good","Excellent"), [PerformanceType]="Y",IIf([PerformanceResult]=0,"No","Yes"))

[ssanfu]

I used nested immediate IFs and the Switch function. It's long!!

Here is the statement:

IIf([PerformanceType]="S",Switch([PerformanceResult]=0,"NA",[PerformanceResult]=1,1,[PerformanceResult]=2,2,[PerformanceResult]=3,3,[PerformanceResult]=4,4,[PerformanceResult]=5,5),IIf([PerformanceType]="R",Switch([PerformanceResult]=0,"Poor",[PerformanceResult]=1,"Fair",[PerformanceResult]=2,"Average",[PerformanceResult]=3,"Good",[PerformanceResult]=4,"Excellent"),IIf([PerformanceType]="Y",Switch([PerformanceResult]=0,"No",[PerformanceResult]=1,"Yes"),"ERROR")))

Here is the SQL for a query:

SELECT EvaluationAllInfo.PerformanceType, EvaluationAllInfo.PerformanceResult, IIf([PerformanceType]="S",Switch([PerformanceResult]=0,"NA",[PerformanceResult]=1,1,[PerformanceResult]=2,2,[PerformanceResult]=3,3,[PerformanceResult]=4,4,[PerformanceResult]=5,5),IIf([PerformanceType]="R",Switch([PerformanceResult]=0,"Poor",[PerformanceResult]=1,"Fair",[PerformanceResult]=2,"Average",[PerformanceResult]=3,"Good",[PerformanceResult]=4,"Excellent"),IIf([PerformanceType]="Y",Switch([PerformanceResult]=0,"No",[PerformanceResult]=1,"Yes"),"ERROR"))) AS xyz
FROM EvaluationAllInfo;

Another way would be to write a UDF to return the result.

Public Function GetResultText(pType As String, pResult As Long) As String
   
   Select Case pType
      Case "S"
         Select Case pResult
            Case 0
               GetResultText = "N/A"
            Case Else
               GetResultText = pResult
         End Select
         
      Case "R"
         Select Case pResult
            Case 0
               GetResultText = "Poor"
            Case 1
               GetResultText = "Fair"
            Case 2
               GetResultText = "Average"
            Case 3
               GetResultText = "Good"
            Case 4
               GetResultText = "Excellent"
            Case Else
               GetResultText = "Error"
         End Select

      Case "Y"
         Select Case pResult
            Case 0
               GetResultText = "No"
            Case 1
               GetResultText = "Yes"
         End Select
      Case Else
         GetResultText = "Error"
   End Select

End Function

SELECT EvaluationAllInfo.PerformanceType, EvaluationAllInfo.PerformanceResult, GetResultText([PerformanceType],[PerformanceResult]) AS xyz
FROM EvaluationAllInfo;

In both queries, change "xyz" to whatever name you want...