Nested IIF > CASE

Question

I need help eliminating hard to follow nested IIF statements embedded in a query field’s criteria.  Can I create CASE statement(s) in a module to be used in the query?

Here's an example mess that would be typical:

Example: IIf(([Address_Fail]=1 Or [DOB_Fail]=1) And [P2Validation]![AccountId] Is Null,1,IIf([SN_Fail]=1 And [P2Validation_SN]![AccountId] Is Null,1,IIf([dbo_AccountTypes]![Code]<>"R" And [dbo_AccountApplicationMatchedRepIPsQ]![AccountId] Is Not Null And [P2Validation_IP_Application]![AccountId] Is Null And [P2Validation_IP_SCA]![AccountId] Is Null,1,IIf([dbo_AccountTypes]![Code]="R" And [dbo_AccountApplicationMatchedRepIPsQ]![AccountId] Is Not Null And [P2Validation_IP_Application]![AccountId] Is Null And [P2Validation_IP_SCA]![AccountId] Is Null And [P2Validation_IP_SR]![AccountId] Is Null,1,0))))

The above is an example of what I have been doing for YEARS.  Adding nested IIF’s to a query field's criteria which is difficult to follow.  Can you show a novice a better way to do this using the example above?  Step by step if you were to convert this mess to CASE?

Thank you in advance!!

Answer 1

This is a bit of a complex function to start out. Here is a smaller example that calculates the circumference based on the radius.

Public Function Circumference(dblRadius As Double) As Double

    Const dblPi As Double = 3.14159265358979  'close enough

    Circumference = 2 * dblPi * dblRadius

End Function

Make sure when writing code, you regularly click Debug->Compile in the ribbon to catch any syntax errors. This won't find logic errors which comes from trial and error.

You can test this by opening the debug window (press Ctrl+G) and entering:

? Circumference(5)

This should return 31.4159265358979

Answer 2

Very kind!  Thank you!  I will give this a try and report back.

Answer 3

You can use a user-defined function like you would use almost any other function. Create a new blank module and paste the code which again might look like:

Public Function GetExample(intAddressFail As Integer, intDOBFail As Integer, _

          varSNAccountID As Variant, varIPsQAccountID As Variant, _

          varAppAccountID As Variant, varSCAAccountID As Variant, _

          intSNFail As Integer, strCode As String) As Integer

      '=====  ORIGINAL SYNTAX  =========

      'IIf(([Address_Fail]=1 Or [DOB_Fail]=1) And [P2Validation]![AccountId] Is Null,1,

      '   IIf([SN_Fail]=1 And [P2Validation_SN]![AccountId] Is Null,1,

      '   IIf([dbo_AccountTypes]![Code]<>"R" And [dbo_AccountApplicationMatchedRepIPsQ]![AccountId] Is Not Null And

      '       [P2Validation_IP_Application]![AccountId] Is Null And [P2Validation_IP_SCA]![AccountId] Is Null,1,

      '   IIf([dbo_AccountTypes]![Code]="R" And [dbo_AccountApplicationMatchedRepIPsQ]![AccountId] Is Not Null And

      '       [P2Validation_IP_Application]![AccountId] Is Null And [P2Validation_IP_SCA]![AccountId] Is Null And [P2Validation_IP_SR]![AccountId] Is Null,1,0))))

      Select Case True

          Case intAddressFail = 1 And varSNAccountID Is Null  'Address failed and no SNA AccountID

              GetExample = 1

          Case intSNFail = 1 And varSNAccountID Is Null

              GetExample = 1

          'More Case statements for other conditions

     End Select

End Function

In a query or code or control source you could call the function like:

Example: GetExample([Address_Fail], [DOB_Fail], [P2Validation_SN]![AccountId],[dbo_AccountApplicationMatchedRepIPsQ]![AccountId], [P2Validation_IP_Application]![AccountId], [P2Validation_IP_SCA]![AccountId], [SN_Fail], [dbo_AccountTypes]![Code])

I might have missed one of the AccountIDs.

Answer 4

Thank you for getting me started.  I'm not sure that I understand how to use your example.  How I use it in the query.

Answer 5

I would definitely create a user-defined function that accepts the field values and returns 1 or 0. I don't understand all of your logic but to get you going your basic public function in a standard module might start out like:

Public Function GetExample(intAddressFail as Integer, intDOBFail as Integer, _

      varSNAccountID as variant, varIPsQAccountID as variant, _

      varAppAccountID as variant, varSCAAccountID as variant, _

      intSNFail as Integer, strCode as String) As Integer

      'Logic code and comments here

End Function

I typically save these in a module named "modBusinessCalcs".

You will need to add comments and logic and finish with a line like:

GetExample = ...

You can also use the Switch function but it is still overly complex.