Lower of in a query

[PJ]

I have 3 text fields in a table that is tied to a form. You can type any
"dollar amount" or a word "Exception Approval" in the three fields. If I
did a query based off of the table looking for lowest dollar amount in the
three field unless "Exception Approval" is select then that would bet out the
lowest amount. How would I do a expression?

Thanks in advance!!

 

[Michel Walsh]

SELECT MIN(val(field1))
FROM tableName
WHERE IsNumeric(field1)


That is a poor design, though, since your amount are now stored as TEXT, not
as value. It would have been preferable to define a currency field which
would allow NULL in the field, and to leave the field empty, rather than
typying a text in it.





Vanderghast, Access MVP

 

[KARL DEWEY]

UNTESTED UNTESTED

SELECT IIF(MIN(val(field1)) < MIN(val(field2)), IIF(MIN(val(field2)) <
MIN(val(field3)), MIN(val(field2)), IIF(MIN(val(field1)) < MIN(val(field3)),
MIN(val(field1)), MIN(val(field3))))) AS Lowest_Value
FROM tableName
WHERE IsNumeric(field1) Or IsNumeric(field2) Or IsNumeric(field3);