Report Calcuation

[Guest]

Hi everyone,
I need to create a field in a report that will have a control source of
which is the lowest price between [price1],[price2],[price3]. I would also
have to allow for null or zero values. Thanks for any info.

Bruce D.

 

[Tom Collins\(Home\)]

| Hi everyone,
| I need to create a field in a report that will have a control source
of
| which is the lowest price between [price1],[price2],[price3]. I
would also
| have to allow for null or zero values. Thanks for any info.
|
| Bruce D.
|

You need a function within a module to do this. Try this:

Function MinPrice(ParamArray Price()) As Currency
Dim TempPrice
Dim I As Integer
TempPrice = Nz(Price(0), 0)
For I = 1 To UBound(Price)
Price(I) = Nz(Price(I), 0)
If Price(I) < TempPrice Then
TempPrice = Price(I)
End If
Next I
MinPrice = CCur(TempPrice)
End Function

This will accept as many variables you want. It will assume Nulls are
zero. Change the Currency to whatever datatype you need.
Call this with =MinPrice([price1],[price2],[price3])

Tom Collins