In query designer how to use IF,Elseif,else

Posted on 2007-08-08
Last Modified: 2013-11-05
In access query designer how can I put an if condition. Like if fieldA = this or that then Red.
Else if
fielda = new or old then green
fieldA = yellow.
Question by:cdb424ttm
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Switch([FieldA=this or [Fielda] = that,"RED",[FieldA]=New Or [FieldA]= old,That ......)

    LVL 75

    Accepted Solution

    sorry ....

    Switch([FieldA=this or [Fielda] = that,"RED",[FieldA]=New Or [FieldA]= old,"GREEN", FieldA<>this AND [Fielda]<> That AND FieldA<>Old and FieldA<>New, "YELLOW"      )
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)

    Switch Function

    Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.


    Switch(expr-1, value-1[, expr-2, value-2 & [, expr-n,value-n]])

    The Switch function syntax has these parts:

    Part Description
    expr Required. Variant expression you want to evaluate.
    value Required. Value or expression to be returned if the corresponding expression is True.


    The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

    Switch returns a Null value if:

    None of the expressions is True.

    The first True expression has a corresponding value that is Null.
    Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.
    LVL 2

    Expert Comment


    Are you using the query in a report or form? If that is the case, in the report or form design use conditional formating. Hope this helps.

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "In access query designer"

    LVL 119

    Expert Comment

    by:Rey Obrero


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now