formula in a query

[Beth]

This should be simple, but I am not getting the results I want, so I'm
asking for help with a formula to give me the lowest value from 3 fields, as
long as there is a value in the field and it is greater than 0.
The fields are Test1, Test2, and Test3 and the default for all the fields is
null.
If someone doesn't have a value for any of the tests, or if they score a 0
on one of the tests, I don't want it to show as their best test score.
Thanks in advance for the help.
Beth

 

[Guest]

Rather than a formula, here is a function that will do that calculation for
you. Put it in a standard module so the query will find it. If the highest
score found is <= 0, it will return a null. You can call it from your query
using a calculated field:
BestScore: BestTest([Test1], [Test2], [Test3])

Function BestTest(varTest1 As Variant, varTest2 As Variant, varTest3 As
Variant) As Variant
Dim varHighScore As Variant

varTest1 = Nz(varTest1, 0)
varTest2 = Nz(varTest2, 0)
varTest3 = Nz(varTest3, 0)

varHighScore = varTest1

If varTest2 > varTest1 Then
varHighScore = varTest2
End If
If varTest3 > varHighScore Then
varHighScore = varTest3
End If
BestTest = IIf(varHighScore <= 0, Null, varHighScore)
End Function