This is what my expression is:

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

I keep on getting an error that I have the wrong number of arguments.

Can anyone please tell me what is wrong with my statement?

thanks in advance,

joëlle

...also can anyone point me in the right direction to find out how to do a search engine for my database?...

You need a true part and a false part for each iif(). It appears as though you don't have a false part for either of you iif()'s.

iif(condition to test, if true return this part, if false return this part)

edit - You need a false part for your second iif().

ok, what if the false part is just that I don't want it to do anything?

I kind of had an idea that it might be something like that..i just have no idea how to go about it.

is it like.. iif (), (this is the false part), (this is the true part)??

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

this still gives me the same error but is this closer to what I'm aiming for?

and what kind of false statements usually go there?

joëlle

I suppose you could just tell it to put a "" in the false part...(or maybe "n/a")

Joelle,

In your example:

Profit: IIf ([ExtendedCost])

The parser will conclude that your IIf statement is finished when it encounters
the right parenthesis. You don't have to put the field names in parentheses.


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


Wayne

If SingleCost may be null, it is a little dangerous to use it (in the true path) without verifiying that it is not null.

thanks for the help.. that was great.. I'm also having a bit of an issue with this one, I've tried eliminating as many parantheses as possible but still can't get it to work.


Cost:
IIf(IsNull([PaintCost])
And IsNull([CarpetCredit])
And IsNull([1/4Round])
And IsNull([RailingCost]),0,
[Cost_Unit]*[Quantity]+nz([PaintCost])-nz([CarpetCredit])+nz([1/4Round])+nz([RailingCost]),
IIf (IsNull([PaintCost])
And IsNull([CarpetCredit])
And IsNull ([1/4Round])
And IsNull ([RailingCost]), [Cost_Unit]*[Quantity]))

Hopefully it's not too messy.


hmm..I removed the zero and got the second part of it to work but still can't get that first IIf working.


Nevermind.. I got it to work.. :) Which is awesome for me since I'm just learning all this stuff and really have appreciated all the help given out.

joëlle

J,

Don't know what you're intending, but it breaks down at the "<-- False"
After it resolves the "False", What's the rest???

As far as the parser is concerned, the IIf statement is complete.


Cost: IIf(IsNull([PaintCost]) And IsNull([CarpetCredit]) And IsNull([1/4Round]) And IsNull([RailingCost]), <-- Condition
0, <-- True
[Cost_Unit]*[Quantity]+nz([PaintCost])-nz([CarpetCredit])+nz([1/4Round])+nz([RailingCost]), <-- False
IIf (IsNull([PaintCost])
And IsNull([CarpetCredit])
And IsNull ([1/4Round])
And IsNull ([RailingCost]), [Cost_Unit]*[Quantity]))


Wayne

When you combine multiplication/division and addition/subtraction in a single calculation, be careful to place parentheses in the correct places to ensure the calculation does what you intend. Remember multiplication/division operations are performed prior to addition/subtraction operations so -
a + (b * c) is different from (a + b) * c