Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    17

    Smile Unanswered: Query Builder If.. Then Statements

    I'm trying to figure out how to add an IF..Then statement to a query in MS Access 2002's query builder. Basically, I've got a field where I do a SELECT MAX(Temperature) AS MaxTemp
    statement and if there are no values to evaluate, I'd like to assign a default value of 0 for MaxTemp. Any ideas? Thanks in advance!!

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Query Builder If.. Then Statements

    Originally posted by Nigel_Tufnel
    I'm trying to figure out how to add an IF..Then statement to a query in MS Access 2002's query builder. Basically, I've got a field where I do a SELECT MAX(Temperature) AS MaxTemp
    statement and if there are no values to evaluate, I'd like to assign a default value of 0 for MaxTemp. Any ideas? Thanks in advance!!
    You can use an IIf function (Immediate If).

    IIf(condition 1, result 1, result 2)

    This is from the help files:

    IIf Function


    Returns one of two parts, depending on the evaluation of an expression.

    Syntax

    IIf(expr, truepart, falsepart)

    The IIf function syntax has these named arguments:

    Part Description
    expr Required. Expression you want to evaluate.
    truepart Required. Value or expression returned if expr is True.
    falsepart Required. Value or expression returned if expr is False.



    Remarks

    IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

    Hope this helps.

    Gregg

  3. #3
    Join Date
    Jan 2004
    Posts
    17

    Question

    Gregg - thanks for your response. I guess I'm not sure how I could use the IIf statement to return the MAX value from the SELECT statement if, infact, there is a MAX value to return. For example, the following code does not work:
    SELECT Max(Temperature) AS MaxOfTempDecember
    FROM ExportData
    WHERE Month([Date])=12
    IIF(IS NULL, 0, MaxOfTempDecember)

    Any have a suggestion?

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Nigel_Tufnel
    Gregg - thanks for your response. I guess I'm not sure how I could use the IIf statement to return the MAX value from the SELECT statement if, infact, there is a MAX value to return. For example, the following code does not work:
    SELECT Max(Temperature) AS MaxOfTempDecember
    FROM ExportData
    WHERE Month([Date])=12
    IIF(IS NULL, 0, MaxOfTempDecember)

    Any have a suggestion?
    Here is the SQL statement that generated info from a table in one of my databases.

    SELECT tblBudgets.BudgetLineItem, IIf(IsNull(Max([RevAmount])),100000,Max([RevAmount])) AS MaxValue
    FROM tblBudgets
    WHERE (((tblBudgets.Year)=2004))
    GROUP BY tblBudgets.BudgetLineItem;

    It functions properly and seems to be the same format that you are using. The only difference was that I included another field to group by which returned multiple records.

    I would construct your statement something like the following:

    SELECT IIf(IsNull(Max(Temperature)), 0, Max(Temperature)) AS MaxOfTempDecember
    FROM ExportData
    WHERE Month([Date])=12

    See if it helps.

    Gregg

  5. #5
    Join Date
    Jan 2004
    Posts
    17
    Thanks again, Gregg! You've been very, very helpful!

    SELECT IIf(Max([Temperature]) Is Null,0,Max([Temperature])) AS MaxOfTempJuly

Posting Permissions

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