Can I do an If Then Else in sql??

[PNGBill]

Hi Forum,

My sql below works but I just realised we need two alternatives to a Fields data and not just 1 alternative.

SELECT tblBankStatements.StatementDate AS xDate, "Repayment" AS Type, tblMemberRepayments.PayMethod AS Method, tblBankStatements.BankID AS Reference, 0 AS Interest, 0 AS Principal, 0 AS To_Pay,  tblMemberRepayments.PaymentAmt AS Credit, IIf(Type = "Interest", TBLLOAN.LoanLateFee, 0) AS Late_Fee, 0 AS Balance, " " AS XName, tblMemberRepayments.LoanID AS LDPK, TBLLOAN.ADPK
FROM TBLLOAN 
INNER JOIN (tblBankStatements 
INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID) 
                               ON TBLLOAN.LDPK = tblMemberRepayments.LoanID
UNION ALL SELECT TBLTRANS.TRNACTDTE AS xDate, IIF(TBLTRANS.TRNTYP="Interest", "Int,Princ&Fees",TRNTYP)  AS Type,  TBLTRANS.TRNMTH AS Method, TBLTRANS.TRNREF AS Reference, TBLTRANS.TRNDR AS Interest, TBLTRANS.TRNPR AS Principal, [TBLTRANS.TRNDR]+[TBLTRANS.TRNPR] AS To_Pay,  0 AS Credit, IIF(TBLTRANS.TRNTYP="Late Fee",0-TBLTRANS.TRNDR,TBLLOAN.LoanLateFee) AS Late_Fee, 0 AS Balance, " " AS XName, TBLTRANS.LDPK, TBLLOAN.ADPK
FROM TBLLOAN 
INNER JOIN TBLTRANS ON TBLLOAN.LDPK = TBLTRANS.LDPK
WHERE (((TBLTRANS.TRNTYP)="Interest")) OR (((TBLTRANS.TRNTYP)="Late fee")) OR (((TBLTRANS.TRNTYP)="Legal Fees"));

This part:
IIF(TBLTRANS.TRNTYP="Late Fee",0-TBLTRANS.TRNDR,TBLLOAN.LoanLateFee) AS Late_Fee

Here we make the field Late_Fee to be the Negative value of TRNDR if Late Fee is the value of TRNTYP, otherwise Late Fee will be TBLLOAN.LoanLateFee.

It works but we also want to make
IIF(TBLTRANS.TRNTYP="Legal Fee",0, TBLLOAN.LoanLateFee) AS Late_Fee

If TRNTYP = Legal Fee then just use zero as the Late Fee.

Options are: Interest, Late Fee & Legal Fee - also Repayment but this is covered in the first half of the Union Query.

Any suggestions on how to manage this, if possible, in this one sql would be much appreciated.

 

[PNGBill]

Resolved per Google.

Used nested IIF

IIF(TBLTRANS.TRNTYP="Late Fee",0-TBLTRANS.TRNDR,IIf(TBLTRANS.TRNTYP="Interest",TBLLOAN.LoanLateFee,0)) AS Late_Fee

 

[Banana]

Just as a FYI: nested Iif are easy to build but once we get beyond more than 2 nested, it becomes unwiedly. In such cases, we would use Switch() or Choose() function instead.

Best of luck!

 

[PNGBill]

Just as a FYI: nested Iif are easy to build but once we get beyond more than 2 nested, it becomes unwiedly. In such cases, we would use Switch() or Choose() function instead.

Best of luck!

Thanks Banana, much better then two queries

 

[DCrake]

Also, if you want to switch the signage from positive to negative and vice versa instead of using 0-1 = -1 (which only works one sided) simply 10*-1 = -10 & -10*-1 = 10