Prev: How to Populate a Table based on Entries in Another
Next: Populate Table with Fields from Another Table
From: Sean on 12 Jan 2010 09:36
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,


From: vanderghast on 12 Jan 2010 09:49
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




"Sean" <Sean(a)discussions.microsoft.com> wrote in message
news:2368FD28-6226-4036-9B3A-B5171A9B18CC(a)microsoft.com...
>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,
>
>

 | 
Pages: 1
Prev: How to Populate a Table based on Entries in Another
Next: Populate Table with Fields from Another Table