CODEWITHSUNDEEP
sqlms-access
Katie
Katie

Reputation: 105

If/Then in Access query

I need some help constructing an If/Then query in Access. I want the New Comments field to populate based on the Cardholder, Approver, and Reconciler columns:

If only Cardholder is filled: New Comments field = A
If only Reconciler is filled: New Comments field = B
If only Approver is filled: New Comments field = C
If Cardholder & Reconciler filled:  New Comments field =D
If Cardholder & Approver filled: New Comments field = E
If Cardholder & Reconciler & Approver filled: New Comments field = F
If Reconciler & Approver filled: New Comments field = G

enter image description here

Can you help?

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

In MS-Access, switch is the simpler way to handle multiple conditions:

select . . .
       switch([Cardholder] Is Not Null And [Reconciler] Is Not Null And [Approver] Is Not Null, "F",
              [Cardholder] Is Not Null And [Reconciler] Is Not Null, "D",
              [Cardholder] Is Not Null And [Approver] Is Not Null, "E",
              [Reconciler] Is Not Null And [Approver] Is Not Null, "G",
              [Cardholder] Is Not Null, "A",
              [Reconciler] Is Not Null, "B",
              [Approver] Is Not Null, "C",
              1=1, "H"
             )

Upvotes: 3

Lee Mac
Lee Mac

Reputation: 16015

Since you have three fields, and each may be either Null or Not Null there are 23 = 8 possible cases.

The following will account for each:

NewComments: 
IIf([Cardholder] Is Not Null And [Reconciler] Is Not Null And [Approver] Is Not Null,"F",
    IIf([Cardholder] Is Not Null And [Reconciler] Is Not Null,"D",
        IIf([Cardholder] Is Not Null And [Approver] Is Not Null,"E",
            IIf([Reconciler] Is Not Null And [Approver] Is Not Null,"G",
                IIf([Cardholder] Is Not Null,"A",
                    IIf([Reconciler] Is Not Null,"B",
                        IIf([Approver] Is Not Null,"C",
                            "H"
                        )
                    )
                )
            )
        )
    )
)

The final result "H" corresponds to the case in which all three fields are Null.

The logic could obviously be reversed to remove the Not from each statement, but I've constructed the statement based on the example you provided.

Upvotes: 1

Related Questions