• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1184
  • Last Modified:

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

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
kgittinger
Asked:
kgittinger
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:

Over30: IIf([SumOf30To60DayDebits]>=[Balance] and (([Balance]-[CURRENT])>0),([Balance]-[CURRENT]),[SumOf30To60DayDebits]-[CURRENT])
0
 
puppydogbuddyCommented:
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
 
kgittingerAuthor Commented:
Excellent
0
 
kgittingerAuthor Commented:
Puppydogbuddy:

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

Regards
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now