Microsoft Access Built-In Functions: IIF

	Microsoft Access Built-In Functions: IIF

Introduction

To assist you with checking a condition and its alternative, the Visual Basic language provides a function named IIf. Its syntax is:

Public Function IIf( _
   ByVal Expression As Boolean, _ 
   ByVal TruePart As Variant, _ 
   ByVal FalsePart As Variant _ 
) As Variant

This function operates like an If...Then...Else condition. It takes three required arguments and returns a result of type Variant. This returned value will hold the result of the function.

The condition to check is passed as the first argument:

If that condition is true, the function returns the value of the TruePart argument and the last argument is ignored
If the condition is false, the first argument is ignored and the function returns the value of the second argument

As mentioned already, you can retrieved the value of the right argument and assign it to the result of the function. The expression we saw early can be written as follows:

Private Sub cmdFunction_Click()()
    Dim MemberAge As Integer
    Dim MemberCategory As String

    MemberAge = 16

    MemberCategory = IIf(MemberAge <= 18, "Teen", "Adult")

    MsgBox "Membership: " & MemberCategory
End Sub

This would produce the same result we saw earlier.

In the same, you can call as many IIf functions in the subsequent FalsePart sections as you judge necessary:

Public Function IIf( _
   ByVal Expression As Boolean, _ 
   ByVal TruePart As Object, _ 
   	Public Function IIf( _
   	    ByVal Expression As Boolean, _ 
   	    ByVal TruePart As Object, _ 
   	    	Public Function IIf( _
   	    	    ByVal Expression As Boolean, _ 
   	    	    ByVal TruePart As Object, _ 
   	    		Public Function IIf( _
   	    		    ByVal Expression As Boolean, _ 
   	    		    ByVal TruePart As Object, _ 
   	    		    ByVal FalsePart As Object _ 
			) As Object
		) As Object
	) As Object
) As Object

Here is an example:

Private Sub cmdImmediateIf_Click()
    Dim Score As Integer
    Dim Grade As String
    
    Score = InputBox("Enter Course Grade", "High School Grades", "0")
    
    Grade = IIf(Score >= 90, "A", IIf(Score >= 75, "B", IIf(Score >= 60, "C", IIf(Score >= 50, "D", "F"))))
    MsgBox "Your final grade is " & CStr(Grade)
End Sub