IIF Statement - Query Help Needed

[Sean]

I have a table full of items, below is one example from the table. I am
trying to do a query that will display the longest dimension (as L1) for each
item. Item field is text, all others are numbers.

Here is my data:
Item = Y10227
ItemLength = 8.375
ItemWidth = 9.25
ItemHeight = 5.125

Here is the field in my query to determine the largest of the dimensions:
L1: IIf([ItemLength]>[ItemHeight] And
[ItemWidth],[ItemLength],IIf([ItemHeight]>[ItemLength] And
[ItemWidth],[ItemHeight],IIf([Itemwidth]>[itemheight] And
[itemlength],[itemwidth])))

The result I am getting is L1 = 8.375 which is not the largest of the
dimensions, ItemWidth at 9.25 is. What am I missing?

Thanks,

 

[vanderghast]

You have use the complete formulation, not the shortened one, ie.

ItemLength>[ItemHeight] And ItemLength>[ItemWidth]

not

ItemLength>[ItemHeight] And [ItemWidth]


So:


switch( ItemLength>=ItemHeight And ItemLength>=ItemWidth, ItemLength,
itemHeight >= ItemWidth, itemHeight,
true, itemWidth )


Note that switch arguments work in pair, returning the second element of the
first pair having its first element evaluate to true. It is somehow easier
to maintain a switch than multiple iif.

Also note that if ItemLength>=ItemHeight And ItemLength>=ItemWidth is
false, then either Height either Width is the maximum, so it is not
necessary to compare Length again, so only a test like itemHeight >=
ItemWidth is necessary, in this case, and if it is ALSO false, then Width is
necessary the maximum value, so no test is required, thus our last 'pair' :
true, itemWidth.



Vanderghast, Access MVP