Consulting

Results 1 to 3 of 3

Thread: If then in Access Query Expression

  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,383
    Location

    If then in Access Query Expression

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

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    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?
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,383
    Location
    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.
    Last edited by Aussiebear; 06-21-2024 at 03:48 PM.
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •