Solved

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

Posted on 2009-05-02
1,169 Views
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!
0
Question by:kgittinger

LVL 119

Accepted Solution

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

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])
0

Author Closing Comment

Excellent
0

Author Comment

Puppydogbuddy:

Regards
0

## Featured Post

### Suggested Solutions

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.