IIF Problem (syntax?)

[joellita]

Ok so this is the part I've done:

Profit: IIf ([ExtendedCost]) =0, ([Retail for one Cost]) - ([SingleCost]),
IIf ([SingleCost])=Null,([Retail for Multiple Costs]) - ([ExtendedCost]))

This is an access query.. Basically I want it to say that if the ExtendedCost value is 0 then do: retail for one cost - single cost if the extended cost value is not zero (or singlecost is null) then: retail for multiple costs - extendedcost.

any help would be really really appreciated.

joëlle

 

[dhookom]

First issue I see is " =Null". Nothing can equal Null. You can test for null by using
IIf(IsNull([SingleCost]), , )

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[joellita]

Profit: IIf ([ExtendedCost]) = 0, ([Retail for one Cost]) - ([SingleCost]),
IIf (IsNull([SingleCost]), ([Retail for Multiple Costs]) - ([ExtendedCost]))

ok so I changed it to this. The error that I'm getting is that I have the wrong number of arguments.

joëlle

 

[dhookom]

Your second IIf() doesn't have an "if false" argument. What value do you want to return if ExtendedCost <> 0 and SingleCost is not null?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GingerR]

also, get rid of the first ")" to the right of [ExtendedCost]:

Profit: IIf ([ExtendedCost] = 0, ([Retail for one Cost] - [SingleCost]),
IIf (IsNull([SingleCost]), ([Retail for Multiple Costs] - [ExtendedCost]))


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244