IIF statemenst in query not working??

[KevinSlater]

Hi, i have the below code in a field in a query

PD1ACTFORVAL: IIf([ACTUALS_TO_PD]>=1,[PD1ACTVAL],IIF([FORECAST_1_INC_BASE]=TRUE,[FORECAST_1_BASE_VALUE]+[FORECAST_1_PROMO_VALUE]),[FORECAST_1_PROMO_QTY])

but a message says:"the expression you entered has the wrong number of arguments" .....Any ideas what im doing wrong?

(if forecast_1_inc_base is false id like just the last part of code to be done...ie FORECAST_1_PROMO_QTY)

Any help would be great, cheers

 

[sparky2005]

i think your recieving an error message because you have to use the format:

IIf(expr, truepart, falsepart)

in your first statement, you have given the expression and the true part, but not the false part; instead you have gone onto another argument!

try changing it around abit and c if it works!

i will have a go and get back to u...

 

[Brianwarnock]

I think you are missing a closing bracket

 

[KevinSlater]

Ok thanks sparky i have tried playing around with the code but still no luck, ive tried adding a false statement to the first if statement but still no luck, what you said sounds right, i will try again shortly.

If you get chance to have another look yourself that would be appreciated

 

[Brianwarnock]

You can nest IIfs so that is not the problem, but as quoted there are more ( than )

Brian

 

[KevinSlater]

glad you can nest iif statements in a query, hmm i looked several times brian & ive got 2 left brackets ie 2 of these: ( and 2 right ones ie 2 of these: )
any other suggestions?...im still trying now...

unless my eyeballs have gone funny, which is quite possible.

 

[Brianwarnock]

Sorry missed 1st left, move it to the end.

your expression reads IIf(criteria,true,iif(criteria,true), exp going nowhere

shoukld be IIf(criteria,true,iif(criteria,true,false))

Brian

 

[KevinSlater]

Ok brian, ive tried that but still not working :-(

is this what you meant?
PD1ACTFORVAL: IIf([ACTUALS_TO_PD]>=1,[PD1ACTVAL],IIF[FORECAST_1_INC_BASE]=TRUE,[FORECAST_1_BASE_VALUE]+[FORECAST_1_PROMO_VALUE]),[FORECAST_1_PROMO_QTY]))

a message with this code says "you must enclose iif functions arguments with parentheses"

 

[Brianwarnock]

Not quite, you need to delete the first ) thus
PD1ACTFORVAL: IIf([ACTUALS_TO_PD]>=1,[PD1ACTVAL],IIF([FORECAST_1_INC_BASE]=TRUE,[FORECAST_1_BASE_VALUE]+[FORECAST_1_PROMO_VALUE],[FORECAST_1_PROMO_QTY]))
Just spotted that you had deleted 2nd (

Brian

 

[KevinSlater]

hmmm strange just tried that but still does the same (same error message)

 

[Brianwarnock]

I've got to go now for a while, I see that you are probably retping the code as one IIf is IIF so could you copy and paste it into a post to see if there are any other glitches else I'm beat

Brian