Less of Two...but not ZERO

[cnistler]

I'm looking to take the lesser of 2 data values (in different colums) but
only when the data is greater than 100. Here's an example:

Col A Col B Desired result
664 0 664
803 742 742
665 33 665
0 745 745
42 84 42

I'm familar with using an Iff statement to return the lesser of the two
values but I am running into problems because I need it to return the lesser
of two values ONLY WHEN either column A or B is greater than 100. If neither
column A or B is greater than 100, column 1 is the desired result.

Note: I'm not to familar with SQL so if there is a way to do this in Iff
form, I'd perfer the Iff--otherwise I'll learn.

Thanks for the help!

 

[Amy Blankenship]

I'm looking to take the lesser of 2 data values (in different colums) but
only when the data is greater than 100. Here's an example:

Col A Col B Desired result
664 0 664
803 742 742
665 33 665
0 745 745
42 84 42

I'm familar with using an Iff statement to return the lesser of the two
values but I am running into problems because I need it to return the
lesser
of two values ONLY WHEN either column A or B is greater than 100. If
neither
column A or B is greater than 100, column 1 is the desired result.

Note: I'm not to familar with SQL so if there is a way to do this in Iff
form, I'd perfer the Iff--otherwise I'll learn.

SELECT IIF([Col A]> 100 OR [Col B] > 100, IIF([Col A] < [Col B], [Col A],
[Col B]), [Col A]) FROM YourTable

HTH;

Amy

 

[Guest]

Amy,

Your IIF statement fails on his first set of data (664 and 0). In this
case, the data passes your first test [A]>100 or > 100, but since <
[A], it chooses , which is zero.

I think the following will work, but would be hard to read if you were
trying to debug the query:

IIF([Col A]> 100 AND [Col B] > 100, _
IIF([Col A] < [Col B], [Col A], [Col B]), _
IIF([Col B] > 100, [Col B], [Col A]))

Personally, I prefer to use User defined functions, which you can document
and make much easier to read

Public Function fnMinGtr100(Value1 as long, Value2 as long) as long

IF Value1 > 100 and Value2 > 100 then
'If both values are greater than 100, select the smaller
If Value1 < Value2 then
fnMinGtr100 = Value1
Else
fnMInGtr100 = Value2
endif
elseif Value2 > 100 Then
'if both values are not > 100, but the second is, then the first
'must be less than 100, so take the second
fnMinGtr100 = Value2
else
'If the first value is greater than 100 and the second isn't, or
neither is
'greater than 100, the take the first value
fnMinGtr100 = Value1
endif

end function

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I'm looking to take the lesser of 2 data values (in different colums) but
only when the data is greater than 100. Here's an example:

Col A Col B Desired result
664 0 664
803 742 742
665 33 665
0 745 745
42 84 42

I'm familar with using an Iff statement to return the lesser of the two
values but I am running into problems because I need it to return the
lesser
of two values ONLY WHEN either column A or B is greater than 100. If
neither
column A or B is greater than 100, column 1 is the desired result.

Note: I'm not to familar with SQL so if there is a way to do this in Iff
form, I'd perfer the Iff--otherwise I'll learn.

SELECT IIF([Col A]> 100 OR [Col B] > 100, IIF([Col A] < [Col B], [Col A],
[Col B]), [Col A]) FROM YourTable

HTH;

Amy

 

[cnistler via AccessMonster.com]

THANK YOU, THANK YOU, THANK YOU!

I had previously tried Amy's method without success. Your suggestion did
exactly what I needed and it worked out perfectly.

~Cara

Amy,

Your IIF statement fails on his first set of data (664 and 0). In this
case, the data passes your first test [A]>100 or > 100, but since <
[A], it chooses , which is zero.

I think the following will work, but would be hard to read if you were
trying to debug the query:

IIF([Col A]> 100 AND [Col B] > 100, _
IIF([Col A] < [Col B], [Col A], [Col B]), _
IIF([Col B] > 100, [Col B], [Col A]))

Personally, I prefer to use User defined functions, which you can document
and make much easier to read

Public Function fnMinGtr100(Value1 as long, Value2 as long) as long

IF Value1 > 100 and Value2 > 100 then
'If both values are greater than 100, select the smaller
If Value1 < Value2 then
fnMinGtr100 = Value1
Else
fnMInGtr100 = Value2
endif
elseif Value2 > 100 Then
'if both values are not > 100, but the second is, then the first
'must be less than 100, so take the second
fnMinGtr100 = Value2
else
'If the first value is greater than 100 and the second isn't, or
neither is
'greater than 100, the take the first value
fnMinGtr100 = Value1
endif

end function

HTH
Dale
[quoted text clipped - 22 lines]

Amy