How to use multiple IIf statements in a query

[jabadoojr]

I have a query where I need to use two IIF statements in one field. While they both work seperately, I am having trouble joining them in one statement. Below is the statement I'm working with:





Expr2: IIf([Gifts with Names]![Type]="Pledge" And [SumOfAmount] Is Null,0,[SumOfAmount], IIf([Gifts with Names]![Type]="Outright Gift",[Gifts with Names]![Gift Amount]))

[JoeM]

To make sure we don't interpret your formula attempt incorrectly, can you tell to us (in English, not formulaically) exactly how you want the formula to work?

[jabadoojr]

In my table "Gifts with Names" I have two types of gifts; Pledges and Outright Gifts. In my table "SumOfAmount" it totals all pledge payments.

The first IIf statement (if the gift type is "Pledge") is used to show pledges minus pledge payments, and if there are no payments, it returns zero. The second IIf statement (if the gift type is "Outright Gift") should return the gift amount.

Again, both of these IIf statments work individually, however we run into problems when we try to combine them. Any help is appreciated!!!

[RayMilhon]

I agree with JoeM To give you the proper statement we need to know more about what you need it to do. However, with that said the issue with the statement you posted is the following

Correct Statement IIF(expression,truepart,falsepart)

Your statement: IIF(expression,truepart,faslepart,IIF(expression,t ruepart)) Since expression evaluates to 1 of 2 possibilities true/false having a 3rd possibility won't work.

Looking at what you have I believe what your looking for is as follows:

IIf([Gifts with Names]![Type]="Outright Gift", [Gifts with Names]![Gift Amount], IIF([Gifts with Names]![Type]="Pledge"And [SumOfAmount] Is Null,0,[SumOfAmount]))

However that is a guess and nothing more.

[jabadoojr]

Sorry for the lack of info, but your answer worked! Thanks so much for the help!