How to write an if - else if statement in an access 2003 query using the expression builder?

Posted on 2009-05-02
Last Modified: 2012-05-06
How to write an "if" - "else if" statement with the expression builder for a query?

The logic for the field is [Over30]:
If debits of 0-60 days >=Balance
Then    if (bal-Current)>0, [Over30] = (Bal-current) , 0
Else     [Over30] = debits of 0-60-current.

I have tried :
Over30: IIf([SumOf30To60DayDebits]>=[Balance],(elseIIf([Balance]-[CURRENT]>0,[Balance]-[CURRENT],0),[SumOf30To60DayDebits]-[Over 30]))

I receive syntax error (comma) in query expression.

Thanks so much!
Question by:kgittinger
    LVL 119

    Accepted Solution


    Over30: IIf([SumOf30To60DayDebits]>=[Balance] and (([Balance]-[CURRENT])>0),([Balance]-[CURRENT]),[SumOf30To60DayDebits]-[CURRENT])
    LVL 38

    Expert Comment

    the iif function in access is like the if function in excel where the if and elseif key words are not explicitly stated, but are id'd by their position in a comma separated list of expressions. i am not sure what you meant by the ",0" in your original expression.

    Over30: IIf([SumOf30To60DayDebits]>=[Balance] And [Balance]-[CURRENT]>0,[Balance]-[CURRENT],[SumOf30To60DayDebits]-[Balance])  

    Author Closing Comment


    Author Comment


    Thank you for the additional explanation.  I had already accepted the previous answer.


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    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…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 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

    22 Experts available now in Live!

    Get 1:1 Help Now