I have three Fields (A - C) which can contain any combination of "Yes","No", or Null.

I'd like to calculate FieldD accordingly:

1. If all three fields are blank, make FieldD = Null.
2. If FieldA = Yes, and all others = No or Null, then FieldD = "A only"
3. If FieldB = Yes, and all others = No or Null, then FieldD = "B only"
4. If FieldA = Yes AND FieldB = Yes, then FieldD = "A & B and/or C"
5. If FieldC = Yes, then FieldD = "A & B and/or C" (regardless of what's in FieldA & B have)

This is the code I have so far:

And this is how is what it's returning:




Attached is a database with this same data and query.

Any suggestions? Thank you.

This post has been edited by foxtrot123: Apr 1 2012, 05:20 PM

Hi: When I get several or more combinations of logical checks, I typically just build a function to take care of it. See if this meets your needs. Note how the function is called in the query, plus the module (function) it calls.

HTH
Joe

Hi Foxtrot

Try:


Logic behind this is:
1. IF C, then overrides all other settings, therefore comes first in nested IIF
2. The Boolean AND takes a higher precedence then 'OR' so comes next
3. Boolean OR has no dependencies , condition (A AND B) are dealt with in step 2 and so A OR B are the last 2 conditions,
finally
4. No conditions met returns 'None'

hth

First off, you should not be storing the value of FieldD. It result of the calculation should be evaluated and displayed on demand. Also, its seems interesting to have a calculated result, then the data is indicating exactly what you want. One more thing, if you have a series of fields that are answered with Yes or No, then you may need to examine your table design to see if you actually need a child table indicating the options of the main record. In other words, from initial impressions your table design may not adhere to normalization guidlines that help you create better databases.

Thanks everyone. I went with the function and it works great. I did try to edit it to return a Null instead of the string, "No expected combination" (i.e., "GetResult = Null"). But this produced an Invalid use of Null error, so I changed it to return an empty string (i.e., "GetResult = "" ") which is okay solution. Any suggestions on how to return a Null? Maybe just eliminate the argument from the function?

Hi again: I put in the "No expected combination" as a general "Else" catch-all. I didn't look for what all the combinations might be in your case, I just followed your requirements and added that as a last catch. What combinations might occur that you would want to have the result be a Null instead of "nothing" or a blank or another comment? Did I miss some possible combination that might occur and not be caught by the NZs in the query function call?

Joe

This post has been edited by RJD: Apr 2 2012, 12:29 PM

One last thing...



"Empty" is produced when a field is null (see the function call in the query). This should keep the "None" in the right place and return a Null when all are Null (read: "Empty" in this case).

HTH
Joe

This post has been edited by RJD: Apr 2 2012, 12:36 PM

Joe:

Just wanted to get back to you to say thanks.

My original example of data didn't include a case when all three variables were blank, and for those I wanted to return a Null.

The final function you provided with the "Empty" argument did the trick. Thank you!

Joe:

One quick question ... Does "< "Yes" ..." translate to "If the answer is less than (in the alphabetical sense) "Yes" ..."

In other words, text that's < "Yes" would include "No" because N comes before Y.

That is correct. I just used the alpha sequence to get "anything less in the alpha sequence than Yes" - as in "No" or "Empty". Works out well when I substitute the word "Empty" for Null in a field (in the function call in the query). There are probably several ways you could approach this, but we know this one works.

Joe

This post has been edited by RJD: Apr 6 2012, 04:58 PM