Issue with iif in query

[Angel69]

I run a report twice; once at the beginning of the quarter and once at the end. I need to check if the 'customer score' field has changed up or down in the quarter end report and count how many of each.

I created this iif statement to assign a number to the rating so that I can then count the number of low, minimal, moderate, etc.

My issue is that I'm getting an error "the expression you entered has a function containing the wrong number of arguments" What is wrong with my formula???

BOP_Rating: iif([12-1-13].[Customer Risk Level at Latest Score]="Low",1,iif([12-1-13].[Cusomer Risk Level at Latest Score]="Minimal",2),iif([12-1-13].[Customer Risk Level at Latest Score]="Moderate",3),iif([12-1-13].[Customer Risk Level at Latest Score]="High",4,5))

TIA

 

[pr2-eugin]

Try.

IIF([12-1-13].[Customer Risk Level at Latest Score] = "Low", 1, IIF([12-1-13].[Cusomer Risk Level at Latest Score] = "Minimal", 2, IIF([12-1-13].[Customer Risk Level at Latest Score] = "Moderate", 3, IIF([12-1-13].[Customer Risk Level at Latest Score]= "High", 4, 5))))

 

[Angel69]

That worked! Thanks.

 

[pr2-eugin]

No problem ! Although I would normally use functions if planning on complicating IIF's.

Copy the code into a Standard module,

Public Function getScore(riskLevel As String) As Long
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Select Case riskLevel
        Case "Low"
            getScore = 1
        Case "Minimal"
            getScore = 2
        Case "Moderate"
            getScore = 3
        Case "High"
            getScore = 4
        Case Else
            getScore = 5
    End Select
End Function

Save it by giving it a name different from getScore followed by a Compile. Then use it in the Query..

BOP_Rating: getScore([12-1-13].[Customer Risk Level at Latest Score])