If then in Access Query Expression

[gmaxey]

I have a table (lets call is tblTest) with a role field. For simplicity the roles are A and B. For each record in the table one person performs role A and another person performs role B. In this same table there is an "A Due Date" and a "B Due date" field.

In a query, I need as single "Due_Date" field (there is no actual field named "Due Date" in the table) such that if the Role is "A" then the date returned is "A Due Date" and if the role is "B" the date returned is "B Due Date"

Something like If tblTest.Role = "A" Then tblTest.A_Due_Date Else tblText.A_Due_Date


How would I write this expression and how do I get the "Due Date" field to show in the query?

Thank you.

[June7]

Use IIf() function.

IIf(Role="A", A_Due_Date, B_Due_Date)

But if a record can have only one role, why have two Due Date fields?

[gmaxey]

Perfect. Thank you. It is not my database. Trying to help someone else and I am about one step up from a complete novice. But to answer your question (or try)

The record is tracking a process. There are always two parties involved in the process. The first party may play role A or role B. The other party then serves the other role. Both roles in the process have a due date "A_Due_Date" and "B_Due_Date" which will be different.

The query is to return all the records where A_Due_Date is displayed as "Due_Date" if Party 1 is assigned role A or all the records where B_Due_Dsate is displayed as "Due_Date" if Party 1 in assigned role B.

Thank you.