If Statement or ?

  • Thread starter Thread starter Sean
  • Start date Start date
S

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?
 
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
 
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;
 
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



Sean said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top