Iif elseiif statement for a query (1 Viewer)

K

kat50

Registered User.

Local time

Today, 08:22

Joined

May 2, 2009

Messages

16

• May 2, 2009

• #1

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

 

Kiwiman

Registered User

Local time

Today, 13:22

Joined

Apr 27, 2008

Messages

799

• May 2, 2009

• #2

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

Code:Copy to clipboard

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

 

K

kat50

Registered User.

Local time

Today, 08:22

Joined

May 2, 2009

Messages

16

• May 2, 2009

• #3

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...

 

Kiwiman

Registered User

Local time

Today, 13:22

Joined

Apr 27, 2008

Messages

799

• May 2, 2009

• #4

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...

Code:Copy to clipboard

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

 

K

kat50

Registered User.

Local time

Today, 08:22

Joined

May 2, 2009

Messages

16

• May 2, 2009

• #5

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

Thanks for your advice.

Have a great day!

 

Kiwiman

Registered User

Local time

Today, 13:22

Joined

Apr 27, 2008

Messages

799

• May 2, 2009

• #6

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.

 

K

kat50

Registered User.

Local time

Today, 08:22

Joined

May 2, 2009

Messages

16

• May 2, 2009

• #7

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!

 

Kiwiman

Registered User

Local time

Today, 13:22

Joined

Apr 27, 2008

Messages

799

• May 2, 2009

• #8

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.

Code:Copy to clipboard

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