Using multiple criteria in IIF statement

[Ken Sheridan]

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".

If those are the only allowed combinations of values you should enforce this constraint with a table-level Validation Rule:

([fldDiscNo] = 0 And IsNull([fldRecordingID]) Or [fldIgnore] = True

PS: I see from the examples you posted earlier that other combinations seem to be allowed, so you'd need to amend the ValidationRule expression accordingly. You might find it easier to constrain the row by what is not allowed.

 

[Consonanza]

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

I finally got there with a few detours along the way. Thank you all for your patience and for the helpful side tips. Its very true that in Access there is always more than one way to skin the cat but given this experience with multiple IIFs, the next time I might just go with ..... the function route!