Writing an IIF statement with multiple iff's

[Stayingahead]

I am pulling financial info from a "table view" from our central office. Problem is all transaction amounts show up as a positive figure. I need to write an IIF statement to show Voided Deposits and withdrawls as negative figures. Below is all the codes I have to include or work with.

TransactionTypeCode TransactionTypeTxt
D Deposit
DC Deposit-Consumer
W Withdrawl
VDC Void Deposit Consumer
TW Transfer Withdrawl
VW Void Withdrawl
VDP Void Deposit Program
VD Void Deposit
TD Transfer Deposit
DP Deposit Program

Below is the IIF statement I wrote, but is not returning any amounts. What do I need to do different to get my amounts to show up correctly?

Transactions: IIf([transactiontypecode]="w",IIf([transactiontypecode]="TW",IIf([transactiontypecode]="vdc",IIf([transactiontypecode]="vdp",IIf([transactiontypecode]="vd",[transactionamt]*-1,[transactionamt]*1)))))

Also, can you write an IIF statement using the "*" wildcard character?

 

[Crowley16]

there's 2 results right, * by -1 or +1...

so why not use patern matching or something to categorise them?

e.g.

iif(TransactionTypeCode = "A" or TransactionTypeCode = "B", *-1, *1)

p.s. F1 for pattern matching within jet sql...

--------------------
Procrastinate Now!

 

[LARiot]

That's not how you use an IIf function.

Try:

Amount  = IIf([transactiontypecode]="w" or [transactiontypecode]="TW" or [transactiontypecode]="vdc" or [transactiontypecode]="vdp" or [transactiontypecode]="vd", [transactionamt] * -1, [transactionamt])

However, I'd use a Select Case statement:

Select Case [transactiontypecode]
Case "W", "TW", "VDC", "VDP", "VD"
    Amount = [transactionamt] * -1
Case Else    
    Amount = [transactionamt]
End Select

 

[PWise]

Why the extra calculation
use

Amount  = IIf([transactiontypecode]="w" or [transactiontypecode]="TW" or [transactiontypecode]="vdc" or [transactiontypecode]="vdp" or [transactiontypecode]="vd", -[transactionamt] , [transactionamt])

for the case Statment

Select Case [transactiontypecode]
Case "W", "TW", "VDC", "VDP", "VD"
    Amount = -[transactionamt] 
Case Else    
    Amount = [transactionamt]
End Select

 

[Stayingahead]

Thanks. That worked. I was trying to make something simple, difficult.

 

[PHV]

A simpler way:
Transactions: IIf([TransactionTypeCode] In ("W","TW","VDC","VDP","VD"),-[transactionamt],[transactionamt])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LARiot]

You can't use IN with an IIf statement, can you? It's only for SQL statements.

 

[PHV]

I thought that the OP was a query grid expression ...