If Statement or ?

[Sean]

I have 4 fields in my table, Item, Length, Width, & Height. For any item the
length is not always the largest dimension, sometimes it could be height or
width depending on how the data was entered. I am tryiing to come up with a
query that will give me the 2 largest dimensions. So 1 item the largest two
dimesions could be length and width, where another item could be height and
width. Any thoughts?

 

[vanderghast]

MaxDim: Choose( length >= width AND length >= height, length, width >=
height, width, true, height)


MinDim: Choose( length <= width AND length <= height, length, width <=
height, width, true, height)


MiddleDim: width + length + height - MaxDim - MinDim




Vanderghast, Access MVP

 

[Gina Whipp]

Sean,

In addition to the advice you got, it should be noted that if, in fact,
those are field names then you should consider changing before it causes you
issues. For a complete list see:
http://allenbrowne.com/AppIssueBadWord.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Sean]

This did not work as it produced null values. I pasted it into my query:

SELECT Dim_Master_Final.Material, Dim_Master_Final.Length,
Dim_Master_Final.Width, Dim_Master_Final.Height, Choose([length]>=[width] And
[length]>=[height],[length],[width]>=[height],[width],True,[height]) AS
MaxDim, Choose([length]<=[width] And
[length]<=[height],[length],[width]<=[height],[width],True,[height]) AS
MinDim, [width]+[length]+[height]-[MaxDim]-[MinDim] AS MiddleDim
FROM Dim_Master_Final;

 

[vanderghast]

Indeed, if one (or more) of the three dimensions is NULL, the result for
MaxDim, MinDim and MiddleDim is also NULL. Is changing this NULL to a zero
works?



MaxDim: Choose( Nz(length,0) >= Nz(width,0) AND Nz(length,0) >=
Nz(height,0), Nz(length,0),
Nz(width, 0) >= Nz( height,0), Nz(width,0),
true, Nz(height,0))



MinDim: Choose( Nz(length,0) <= Nz(width,0) AND Nz(length,0) <=
Nz(height,0), Nz(length,0),
Nz(width, 0) <= Nz( height,0), Nz(width,0),
true, Nz(height,0))



MiddleDim: Nz(width,0) + Nz(length,0) + Nz(height,0) - MaxDim - MinDim




Vanderghast, Access MVP

This did not work as it produced null values. I pasted it into my query:

SELECT Dim_Master_Final.Material, Dim_Master_Final.Length,
Dim_Master_Final.Width, Dim_Master_Final.Height, Choose([length]>=[width]
And
[length]>=[height],[length],[width]>=[height],[width],True,[height]) AS
MaxDim, Choose([length]<=[width] And
[length]<=[height],[length],[width]<=[height],[width],True,[height]) AS
MinDim, [width]+[length]+[height]-[MaxDim]-[MinDim] AS MiddleDim
FROM Dim_Master_Final;

MaxDim: Choose( length >= width AND length >= height, length, width >=
height, width, true, height)


MinDim: Choose( length <= width AND length <= height, length, width <=
height, width, true, height)


MiddleDim: width + length + height - MaxDim - MinDim




Vanderghast, Access MVP