Using multiple criteria in IIF statement

Consonanza

New member
Local time
Today, 01:58
Joined
Jan 7, 2022
Messages
6
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")
 
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.
 
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 = ?
 
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?
 
Last edited:
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":
Code:
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.
 
Last edited:
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":
Code:
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.
 
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?
 
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?
 
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:

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

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

Users who are viewing this thread

Back
Top Bottom