Finding Smallest Nonzero Amount from Several Field

[Lucky927]

Hi, I'm stuck on what I think should be something simple. I have several numeric fields (Number1, Number2, Number3, etc). I need another field (MinNumber) to calculate the smallest nonzero value from the set of numeric fields.

I tried using the Minimum function but that won't find the smallest nonzero value (it only returns zero because one of the numeric fields invariably contains 0). Nested If/Then statements doesn't seem appropriate since there are a few dozen numeric fields to check. TIA.

[Peter1]

Assuming you want to calculate the lowest nonzero value for the relevant record (ie: not all the records in the table or found set) , a calculation could be:

Min ( Case ( Number1 > 0 ; Number1 ) ; Case ( Number2 > 0 ; Number2 ) ; Case ( Number3 > 0 ; Number3 ) )

Peter

[Lucky927]

Bingo! That works perfectly. In looking thru the spaghetti that is my code, I just couldn't get from Point A to Point B.

Thanks again.