Using multiple criteria in IIF statement

[Consonanza]

I have a field in my query whiich has 3 tests:
1 fldDiscNo = 0
2 fldRecordingID is null
3 fldIgnore (a Yes/No field) = True

If tests 1 and 2 are met, the query text field (Wanted) should be "Yes" but if test 3 is met, the result should be "No".

Here's one of the many variations I have tried but it doesn't produce the right results.

Wanted: IIf(([tblDiscsOtherlabels!fldDiscNo]=0 And isnull([tblDiscsOtherLabels!fldRecordingID])) Or [tblDiscsOtherLabels!fldIgnore]=True,"No","Yes")

 

[Gasman]

Test for Test3 first?

 

[DHookom]

Create a small used-defined function that has the three fields as arguments. The function will provide comments and easily understood and updatable logic. Nested iif()s should be avoided.

 

[plog]

If tests 1 and 2 are met, the query text field (Wanted) should be "Yes" but if test 3 is met, the result should be "No".

3 binary criteria yields 8 possibilities. You've only addressed 2.

True True True = "Yes"
True True False = "No"
True False True = ?
True False False = ?
False True True = ?
False True False = ?
False False True = ?
False False False = ?

 

[Gasman]

Create a small used-defined function that has the three fields as arguments. The function will provide comments and easily understood and updatable logic. Nested iif()s should be avoided.

Just been told off on another site for suggesting the same, and not supplying actual code for that function?

In fact that highlighted the fact that one cannot delete their account from a site?

 

[DHookom]

Just been told off on another site for suggesting the same, and not supplying actual code for that function?

I replied from my iPad and expected the OP to give it a try and come back with questions.
Creating a function allows you to encapsulate the logic into a single object that can be used over and over within an application. If/when the logic changes, you have one place to look.

Here is a framework that could be used and saved in a module name "modBusinessLogic":

Public Function GetWanted(intDiscNo As Integer, _
    varRecordingID As Variant, _
    booIgnore As Boolean) As String
   
    'Add comments here about field types and possible values
    '  include the date and author
    '  intDiscNo is the field used to store...
    '  varRecordingID is a ... field with values from ....
    '  booIgnore is a yes/no field that ...
   
    ' comments about the logic you are using
   
    'Add code below that uses Select Case to identify _
     desired results based on the values of the _
     function arguments
   

End Function

As noted by #plog, the specifications are lacking.

 

[Gasman]

I replied from my iPad and expected the OP to give it a try and come back with questions.
Creating a function allows you to encapsulate the logic into a single object that can be used over and over within an application. If/when the logic changes, you have one place to look.

Here is a framework that could be used and saved in a module name "modBusinessLogic":

Public Function GetWanted(intDiscNo As Integer, _
    varRecordingID As Variant, _
    booIgnore As Boolean) As String
  
    'Add comments here about field types and possible values
    '  include the date and author
    '  intDiscNo is the field used to store...
    '  varRecordingID is a ... field with values from ....
    '  booIgnore is a yes/no field that ...
  
    ' comments about the logic you are using
  
    'Add code below that uses Select Case to identify _
     desired results based on the values of the _
     function arguments
  

End Function

As noted by #plog, the specifications are lacking.

Oh, I completely agree, especially as this was in Excel. I have done exactly that several times.
I dare say an Excel expert might be able to do it with built in functions, but I am no Excel or Access expert.

 

[DHookom]

Oh, I completely agree, especially as this was in Excel. I have done exactly that several times.
I dare say an Excel expert might be able to do it with built in functions, but I am no Excel or Access expert.

Are you suggesting this post is related to Excel or did I miss something?

 

[Gasman]

Are you suggesting this post is related to Excel or did I miss something?

No, not at all, just that I would have offered the same advice re a function.

 

[Consonanza]

Thanks for all the feedback. For clarification, this is an Access question.
I’m sorry but I’m no Access expert and I don’t know how to write the function as has been outlined.

Perhaps I could try to make the “business logic” clearer.
I am trying to populate an unbound control on a report. The control (WANTED) will have a value of YES or NO.
There are 3 data fields (among others) in the code behind the report.

1. fldDiscNo (integer)
2. fldRecordingID (integer)
3. fldIgnore (boolean Yes/No)

The rules:
1 if fldIgnore = TRUE then WANTED = NO - that’s it, no further test needed.
2 if fldIgnore = FALSE and fldDiscNo >0 and fldRecordingID is not null then WANTED = NO
Any permutation which doesn’t meet the above 2 rules , means WANTED = YES

Is it nor possible to put that in an IIF statement?

 

[plog]

Yes, I would put it in an IIF statement. You test for the 2 possibilities that result in NO and then have everything else be YES:

IIF((fldIgnore=True) OR (fldIgnore=False AND fldDiscNo>0 AND IsNull(fldRecordingID)=False), NO, YES)

 

[CJ_London]

Try this

Wanted: iif([tblDiscsOtherLabels!fldIgnore]=True,"No", IIf(([tblDiscsOtherlabels!fldDiscNo]=0 And [tblDiscsOtherLabels!fldRecordingID] is null,"Yes","No"))

 

[DHookom]

No, not at all, just that I would have offered the same advice re a function.

Thanks. We are on the same page.

 

[DHookom]

It may be time to get your feet wet in VBA. Create a new, blank module and paste this code into it (I think I got the logic correct):

Public Function GetWanted(intDiscNo As Integer, _
    varRecordingID As Variant, _
    booIgnore As Boolean) As String
  
    'Add comments here about field types and possible values
    '  include the date and author
    '  intDiscNo is the field used to store...
    '  varRecordingID is a ... field with values from ....
    '  booIgnore is a yes/no field that ...
  
    'The rules:
    '   1 if fldIgnore = TRUE then WANTED = NO - that’s it, no further test needed.
    '   2 If fldIgnore = False And fldDiscNo > 0 And fldRecordingID Is Not Null Then WANTED = NO
    '   Any permutation which doesn’t meet the above 2 rules , means WANTED = YES
    
    'Add code below that uses Select Case to identify _
     desired results based on the values of the _
     function arguments
    If booIgnore Then
        GetWanted = "NO"
     Else
        If intDiscNo > 0 And Not IsNull(varRecordingID) Then
            GetWanted = "NO"
         Else
            GetWanted = "YES"
        End If
    End If

End Function

You can then create a query to test the results like:

SELECT tblConsonanza.fldDiscNo,
   tblConsonanza.fldRecordingID,
   tblConsonanza.fldIgnore,
   GetWanted([fldDiscNo],[fldRecordingID],[fldIgnore]) AS Wanted
FROM tblConsonanza;

 

[Consonanza]

Once again thanks for all the replies. I've tried both approaches suggested. To my own surprise I managed to get the function to work. My thanks to DHookom for the mini tutorial - I think I've at least tipped my toe in the water.

As for the Iif approach, no joy. Runnig plog's version, a dialog box asks to enter a parameter.

For CJLondon's code:

Wanted: iif([tblDiscsOtherLabels!fldIgnore]=True,"No", IIf(([tblDiscsOtherlabels!fldDiscNo]=0 And [tblDiscsOtherLabels!fldRecordingID] is null,"Yes","No"))

Putting that in the data source for the "Wanted" field in the report, produces a "Syntax error (comma) in query expression [(Wanted: Iif...."

I thought perhaps a closing bracket was missing at the end of the line (3 x "(" but only 2 x ")") in the example above but adding it made no difference. I still thing there's a bracket missing.

Although we have a solution to my initial request (via the function route) I would still be interested in understanding why the multiple Iif option does not work, if only for my own education.

 

[CJ_London]

I was in my phone and just copy/pasted parts of your code - which might have missed something

Assuming you are only using one table, or if multiple tables in your query and field names are not repeated in those tables, you don’t need to specify the table name! (Which should be a .) and since you don’t have spaces in your field names you don’t need the [ ] characters

Edit- just noticed there are two ( after the second iif, there should only be one

 

[Consonanza]

Well spotted. I’ve made the change but it’s looking for tblDiscsOtherLabels!fldRecordingID - the table name is needed because fldRecording appears in more than 1 table. I’m working on the changes needed to the sql in my report’s open event.

 

[CJ_London]

Change the ! to a dot

 

[DHookom]

Well spotted. I’ve made the change but it’s looking for tblDiscsOtherLabels!fldRecordingID - the table name is needed because fldRecording appears in more than 1 table. I’m working on the changes needed to the sql in my report’s open event.

fldRecordingID is different from fldRecording.

 

[CJ_London]

the table name is needed because fldRecording appears in more than 1 table.

ID is not a good suffix for primary and foreign keys - but fine for other ID’s that are just a field in a table such as a membership ID.

Better to use PK and FK suffixes - avoids this problem and you know which ‘end’ of the relationship each field is

Still a possible issue if you have a query with a parent table and two or more child tables but you are more likely to use the PK