Limitation to the number of nested IIf statements within a calculated field.

[darylharris]

Hello all
I was wondering if there is limitation to the number of IIf statements that can be nested in a calculated field.
Basically I need to assign a particular ‘label code’ to a record that will be derived from data with in several fields within the query. The data from the table (that the query is derived from) will be completely deleted and recreated on occasions, meaning I can not write data back to the table as it would be lost when the re-creation happens.
There is a possibility of many derivations for the ‘label code’ (maybe up to 100) so the nested IIf code will be substantial.
If there is a better way to do what I want I am all ears however I am not a VB programmer so would rather stick to doing within the query design grid, or SQL at a stretch.
Anyway the main focus to the question is there a limitation to the number of nested IIF statements.

Example preliminary test code:


Thanks in advance for any help provided regards.

Daryl

 

[Moniker]

There is a limit of seven nest IIf statements in a query statement, so you're going to end up coding a little. To stay in the QBE, you can use the SWITCH() function, or if you are writing a number of variables based on the value of one or more variables, you'd use a Select Case Statement in code.

SWITCH() Function

SomeVariable = Switch(Test1, Value if Test1 is True, Test2, Value if Test2 is True, ... , TextX, Value if TestX is True)

Therefore, these are the same:

If NumberGrade < 60 Then
    LetterGrade = "F"
ElseIf NumberGrade >=60 and NumberGrade <70 Then
    LetterGrade = "D"
ElseIf NumberGrade >=70 and NumberGrade <80 Then
    LetterGrade = "C"
ElseIf NumberGrade >=80 and NumberGrade <90 Then
    LetterGrade = "B"
Else
    LetterGrade = "A"
End If

As a SWITCH() Function:

LetterGrade = SWITCH(NumberGrade<60,"F", NumberGrade>=60 and NumberGrade<70, "D", NumberGrade>=70 and NumberGrade<80, "C",NumberGrade>=70 and NumberGrade<80, "B",[COLOR="Red"]True, "A"[/COLOR])

The part I put in red is potentially confusing, but look at the switch function again. It looks for the first "test" value to evaluate to True, and it takes whatever immediately follows that as the answer. The word "True" (no quotes) will always evaluate to True, so the ending to that Switch is the equivalent of the "Else" part of the If/Then statement. The bigger difference is that SWITCH isn't limited to seven entries, and switch can be used in queries as well as code.

SELECT CASE Statement

Use the same If/Then example from above. The following is a Select Case statement that does the same thing, but adds one more variable to set, depending on the number grade:

Select Case NumberGrade
    Case Is <60
        LetterGrade = "F"
        GradeDesc = "Failing"
    Case Is >=60 And Is <70
        LetterGrade = "D"
        GradeDesc = "Dummy"
    Case Is >=70 And Is <80
        LetterGrade = "C"
        GradeDesc = "Crappy"
    Case Is >=80 And Is <90
        LetterGrade = "B"
        GradeDesc = "Better"
    Case Is >=90 And Is <=100
        LetterGrade = "A"
        GradeDesc = "Aced it!"
    Case Else '(this means the case is something unexpected, as in a number greater than 100)
        LetterGrade = "?"
        GradeDesc = "FUBAR"
End Select

The difference here is that, unlike IIf and SWITCH(), a CASE cannot be used in Access SQL (it can be used in other variants of SQL, but not Access), but the CASE can be used in code and it can be used to set multiple values (LetterGrade and GradeDesc in my example). However, the case can be setup as a function that can be called from SQL. In other words, you could do this in SQL:

LetterGrade: =GetLetterGrade([NumGrade])

Then turn my above code sample into a function, like this:

Function GetLetterGrade(NumberGrade As Integer) As String

    Select Case NumberGrade
        Case Is <60
            GetLetterGrade = "F"
            GradeDesc = "Failing"
        Case Is >=60 And Is <70
            GetLetterGrade = "D"
            GradeDesc = "Dummy"
        Case Is >=70 And Is <80
            GetLetterGrade = "C"
            GradeDesc = "Crappy"
        Case Is >=80 And Is <90
            GetLetterGrade = "B"
            GradeDesc = "Better"
        Case Is >=90 And Is <=100
            GetLetterGrade = "A"
            GradeDesc = "Aced it!"
        Case Else '(this means the case is something unexpected, as in a number greater than 100)
            GetLetterGrade = "?"
            GradeDesc = "FUBAR"
    End Select

End Function

Now, the value for LetterGrade will be calculated by the custom function GetLetterGrade, so long as you pass the field "NumberGrade" to it from the query.

Simple, eh?

There's a lot more here, but I don't want to get too overwhelming.

HTH

 

[darylharris]

Moniker,

Thankyou so much for your help and reply........
I am slowly working through it and it is making more sense the more I do.
If I cant get it to work, I will have to fly you out to Australia to help lol.
It is beautifil here at the moment... mid summer and te job is very close to Bondi Beach
Again, thanks for your help.
Regards

Daryl

 

[Access_Help]

How do I use a Select case in a calculated query?

I can't nest more than 7 IIFs, can anyone guide me on:

1) Where to write the Select statement (code Builder?)
2) How to point the select case in a calculated field.

 

[Frothingslosh]

How do I use a Select case in a calculated query?

I can't nest more than 7 IIFs, can anyone guide me on:

1) Where to write the Select statement (code Builder?)
2) How to point the select case in a calculated field.

You have to write a custom function the way Moniker said. If you've not written code yet, I would go to the Modules tab, and Create New Module. At the top, it already says Option Compare Database. Directly below that, I would STRONGLY suggest adding Option Explicit.

Then a couple lines below that, you can create the function. Just go through Moniker's example to see how it works, and don't be afraid to use the built-in help (F1). If you still have questions, folks here can certainly help.

As to the second question, could you please elaborate? I don't understand what you're asking.

 

[Access_Help]

Hi,

Thanks for the detailed steps on writing a module.

I completed the steps but I'm getting an #error.

I have a field named CWGRADE (set as text) where a user enters a grade.

I have a calculated field with the following which will display a number based on the grade entered in the CWGRADE field.

ConvertGradeToPercentage:GetMarkForGrade([CWGRADE])

This is my module code:

Option Compare Database
Option Explicit

Function GetMarkForGrade(GetMark As Integer) As String

    Select Case GetMark
        Case Is = "A*"
            GetMark = 90
            
        Case Is = "A+"
            GetMark = 87
        Case Is = "A"
            GetMark = 84
        Case Is = "A-"
            GetMark = 80
       
           
        Case Is = "B+"
            GetMark = 77
        Case Is = "B"
            GetMark = 74
        Case Is = "B-"
            GetMark = 70



       Case Is = "C+"
            GetMark = 67
        Case Is = "C"
            GetMark = 64
        Case Is = "C-"
            GetMark = 60


    Case Is = "D+"
            GetMark = 57
        Case Is = "D"
            GetMark = 54
        Case Is = "D-"
            GetMark = 50
            
            
      Case Is = "E+"
            GetMark = 47
        Case Is = "E"
            GetMark = 44
        Case Is = "E-"
            GetMark = 40
            

Case Is = "F+"
            GetMark = 37
        Case Is = "F"
            GetMark = 34
        Case Is = "F-"
            GetMark = 30



Case Is = "G+"
            GetMark = 27
        Case Is = "G"
            GetMark = 24
        Case Is = "G-"
            GetMark = 20
   Case Is = "U"
            GetMark = 0


    End Select

End Function

I'm getting #error

 

[Frothingslosh]

Try changing

ConvertGradeToPercentage:GetMarkForGrade([CWGRADE])

to

ConvertGradeToPercentage = GetMarkForGrade(Me.CWGRADE)

In VBA, you use = to assign values to variables. In addition, you have to specify when you're referring to a control (it defaults to the Value property, so you don't need to add that). In a query, [CWGRADE] would be correct if that were a field name in the query itself, but for a control, you have to tell the system where to find the control, thus the Me.CWGRADE.

Now, as to the rest of the code, what precisely are you trying to do? Based on how you wrote the function, it looks like you're passing a numeric grade to the function and you want it to return a letter grade. If that's the case, this is the format you would want:

Public Function ConvertGradeToPercentage(ByVal GetMark As Integer) As String
Select Case GetMark
    Case Is >= 90
        ConvertGradeToPercentage = "A*"
    Case Is >= 87
        ConvertGradeToPercentage = "A+"
    Case Is >= 84
        ConvertGradeToPercentage = "A"
    Case Is >= 81
        ConvertGradeToPercentage = "A-"
    Case Else
        ConvertGradeToPercentage = "Not an A"
End Select
End Function

Obviously you would need to extend it to cover all grades.

For a quick lesson, here's a breakdown of what we're doing.

In the function call "Public Function ConvertGradeToPercentage(ByVal GetMark as Integer) as String", here's what your'e doing:

1) Declaring the procedure as public, meaning it can be called from throughout the project (database).
2) Declaring it as a function, meaning it is capable of returning a value.
3) Naming it ConvertGradeToPercentage
4) Providing the argument "ByValGetMark as Integer"
5) Indicating that the function will return a string to whatever called it.

The argument breaks down like this:
1) ByVal means it makes a new copy of the data passed to it, so you can mess with it without touching the data in the procedure that called it.
2) Declaring that the provided data will be referred to as GetMark inside the procedure (and ONLY inside the procedure).
3) Declaring that the provided data MUST be an integer. Anything else will generate an error.

Once the correct CASE is found, a value is assigned to the function name (which functions - pardon the pun - as a variable while executing - and then skips the rest of the cases. Once the procedure ends or exits, the value is returned to the calling procedure.

As to the code, here's info on how to work with SELECT: http://msdn.microsoft.com/en-us/library/cy37t14y.aspx

Basically, you list WHAT you're testing on the SELECT CASE line, and the values you're testing FOR on the individual CASE lines. Once one is found to be true, the code listed below it (until the next CASE line or the END SELECT line) is executed, then it skips down to END SELECT.

Now, back to your function. What I listed up above was how it looked like you wrote your function, but the NAME of your function is the opposite. If you ARE providing a string and wanting it to return a number, you'll need to switch pretty much everything in the sample to be the other way around, using what I described above. If that's the case, you basically need to have the function require a string and return an integer, and you'll SELECT based on the string instead.

Hope this helps!

 

[Frothingslosh]

Ok, rereading, this looks like what you want:

Public Function ConvertGradeToPercentage(ByVal GetMark As String) As Integer
    Select Case GetMark
        Case "A*"
            ConvertGradeToPercentage = 90
        Case "A+"
            ConvertGradeToPercentage = 87
        Case "A"
            ConvertGradeToPercentage = 84
        Case "A-"
            ConvertGradeToPercentage = 80
        Case Else
            'Etc, etc, etc
    End Select
End Function

 

[Access_Help]

hi, thanks!

The function is being passed through a query and not a control.

Please see attached document containing screenshot of what I am aiming to do.

I'm not a programmer but can play about with some code with help. I'm really stuck on this!

Thanks

 

[Frothingslosh]

Basically, the code I put up in post #8 is how you need to format your function. Just fill it out completely with each letter grade a student can get, rather than stopping at A- the way I did.

In the query itself, in the box where you normally put the field name, put this:

GradeNum: ConvertGradeToPercentage([CWGRADE])

I used 'ConvertGradeToPercentage' because that's how my sample function is named. If your function, once you finish rewriting it, has a different name, use that instead.

 

[Access_Help]

thank you soo very much, works perfectly!

Really appreciated!

thank you!

 

[Access_Help]

I think I need to use a function for this problem

Any ideas on how I do this?

 

[Frothingslosh]

Responded in that thread.