How can I write an if elseif statement with the expression builder for a query?

Howzit

Welcome to the forum..

Youn can use the Iif statement - basically a logical stmt.

Format: Logical Expression, Value if True, Value if False

An example

Cat: IIf([tblCategory]![CategoryId]=1,"First",IIf([tblCategory]![CategoryId]=2,"Second","Other"))

I need to do if one thing then this if otherwise that...

Over30: IIf([SumOf30To60DayDebits]>=[ Balance],
iif( [Balance]-[CURRENT]>0, ([Balance]-[CURRENT],0),
([SumOf30To60DayDebits]-[Over 30])))

I am having comma issues...

Howzit

Will this work?

If the Balance minus the current value = 0 then return 0, otherwise return the balance less the current value to give everything over 30...


Over30:iif([Balance]-[CURRENT]=0,0,[Balance]-[CURRENT])

I try giving it a whirl. I guess I cannot do a nested if type statement:(

Thanks for your advice.

Have a great day!

Howzit

You can do a nested iif stmt, just didn't know if it warranted one in this case. I may have missed the point on exactly what you are trying to do - which is likely.

I suppose I should have said "how do I layer an if statement?" I am sorry, beginners don't necessarily know how to ask the right question.

I need to produce a statement as such:

if[SumOf30To60DayDebits]>=[Balance]
Then
if([Balance]-[CURRENT]>0 then ([Balance]-[CURRENT] else 0
but if [SumOf30To60DayDebits]is not >=[Balance] then [SumOf30To60DayDebits]-[CURRENT]

I need the 0 to be returned if appropriate.

The end goal is to produce debit amount in aging buckets.

Thanks a million!

Howzit

Try this then - I think this will get you what you want. I changed the logical expression on the first iif to be < balance, in order to give a straight x-y equation for the True part, then used another iif stmt for the false part.

I definitely find it easier making the true part a simple equation as opposed to another if.




iif([SumOf30To60DayDebits]<[Balance],[SumOf30To60DayDebits]-[CURRENT],iif([Balance]-[CURRENT]>0,[Balance]-[CURRENT],0))