Hi Alison,
Some time ago I wrote a function to do this. You will need to create a
regular (not class, not form, not report) module in your database. Copy and
paste this code into it:
=========================================
Public Function MinimumAny(ParamArray var() As Variant) As Variant
' Minimum function that accepts any number of parameters.
' Parameters: Any number of items accepted
' Returns: The mimimum of all values, if there are any non-null values,
' otherwise Null
' Author: Clifford Bass
Dim i As Integer
Dim j As Integer
Dim varMinimum As Variant
Dim varSubMinimum As Variant
On Error GoTo MinimumAny_Error
varMinimum = Null
For i = LBound(var()) To UBound(var()) Step 1
If Not IsNull(var(i)) Then
If IsArray(var(i)) Then
For j = LBound(var(i)) To UBound(var(i)) Step 1
varSubMinimum = MinimumAny(var(i)(j))
If Not IsNull(varSubMinimum) Then
If IsNull(varMinimum) Then
varMinimum = varSubMinimum
Else
If varSubMinimum < varMinimum Then
varMinimum = varSubMinimum
End If
End If
End If
Next j
Else
If IsNull(varMinimum) Then
varMinimum = var(i)
Else
If var(i) < varMinimum Then
varMinimum = var(i)
End If
End If
End If
End If
Next i
MinimumAny = varMinimum
MinimumAny_Exit:
Exit Function
MinimumAny_Error:
MinimumAny = strError
Resume MinimumAny_Exit
End Function
=========================================
To use it in a query you will enter something like this in the top row
where you normally place the field names:
MinimumDate: MinimumAny([DOB], [First LWS], [First SLDWS], [First SA])
When used in a query you can enter up to 29 (or maybe 30) fields. You
can also use it from VBA code. In which case you can have many more
parameters (fields or values), including arrays of items. It will work with
any kind of data, but you may get strange results if you mix types of data.
Hope that helps,
Clifford Bass
