IIF statements with "null"

[aftershokk]

Hello,

BF Best: IIf([TotalItmMast]![FinalPrice4]=" ",[TotalItmMast]![FinalPrice3],[TotalItmMast]![FinalPrice]) etc...


Above: What I am trying to do is:

If Price 4 is null then populate price 3, if price 3 is null then populate price 2, otherwise populate price 1.

I cannot seem to get this to work!

Any help would be great!!

 

[RV]

Null is not quite the same as " " (in fact, these two can't be compared at all...).

Try this:

IIf([TotalItmMast]![FinalPrice4] Is Null ,[TotalItmMast]![FinalPrice3],[TotalItmMast]![Fina
lPrice])

RV

 

[aftershokk]

null

This does not seem to work.

BF Best: IIf([TotalItmMast]![FinalPrice4] Is Null,[TotalItmMast]![FinalPrice3],IIf([TotalItmMast]![FinalPrice3] Is Null,[TotalItmMast]![FinalPrice2],IIf([TotalItmMast]![FinalPrice2] Is Null,[TotalItmMast]![FinalPrice],[TotalItmMast]![FinalPrice])))

I am trying to say, if price 4 is null then use 3, if 3 is null then use 2, and if 2 is null use price otherwise use price.

Any thoughts?

Thanks!

 

[Jon K]

BF Best:
IIf(not isnull([TotalItmMast]![FinalPrice4]),[TotalItmMast]![FinalPrice4],
IIf(not isnull([TotalItmMast]![FinalPrice3]),[TotalItmMast]![FinalPrice3],
IIf(not isnull([TotalItmMast]![FinalPrice2]),[TotalItmMast]![FinalPrice2],
[TotalItmMast]![FinalPrice])))

 

[aftershokk]

null

That worked great!!!!
I did even think of it that way. Brain Freeze...

Another question....


On my form, I now have two fields created with a query using if/then statements. Is there a function, which will look at both fields and give me the lower price of the two and write it to a table or query? In other words,

Field 1 - $1.99 and Field 2 - $4.00

New field would pull the min based on an array.

New field = $1.99

 

[Jon K]

New Field: iif([Field 1]<[Field 2], [Field 1], [Field 2])

 

[aftershokk]

iif

thanks again, I kept on wanting to use a min function, but I do not think it can be done with arrays.


THANKS AGAIN!

 

[Jon K]

Take a look at this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?threadid=32627

There is an example (in the code in Public Function MinTime()) of looping through an array with the iif() function to find the min value.