min function

  • Thread starter Thread starter Rick G
  • Start date Start date
R

Rick G

Each column contains a Rate, five companies, therefore five rates.
Attempting to determine the lowest rate of the five, to be populated in
an additional column, "Best Rate". (146K records - would do it EASILY
in Excel, just too big)

Trying to use the MIN function in expression builder, avoiding SQL (of
which I am wholly unfamilar with).

When I get the syntax seemingly right, it is giving me a -1 return,
which would seem to be a False response.

Any ideas, or just suck it up and dive into SQL?
 
Each column contains a Rate, five companies, therefore five rates.
Attempting to determine the lowest rate of the five, to be populated in
an additional column, "Best Rate". (146K records - would do it EASILY
in Excel, just too big)
Click to expand...

Well, Excel is a spreadsheet; Access isn't.
Trying to use the MIN function in expression builder, avoiding SQL (of
which I am wholly unfamilar with).
Click to expand...

Min() works *down* a single field, finding the minimum value of that
field in any record. It does NOT work across fields within a record.
When I get the syntax seemingly right, it is giving me a -1 return,
which would seem to be a False response.
Click to expand...

Ummm... care to give us some help here? What expression did you use,
and where?
Any ideas, or just suck it up and dive into SQL?
Click to expand...

Actually with five fields, a SQL query will be rather snarky! I'd
suggest using a VBA function instead:

Public Function MinOfFields(ParamArray FVal() As Variant) As Variant
Dim iPos As Integer
Dim Least As Variant
Least = FVal(0)
For iPos = 1 To UBound(FVal)
If FVal(iPos) < Least Then Least=FVal(iPos)
Next iPos
MinOfFields = Least
End Function

Copy this function into a new Module; save it as basUtilities (*DON'T*
name the module MinOfFields, the name must be unique); and in a vacant
Field cell in a query based on your table type

TheMinimum: MinOfFields([FieldA], [FieldB], [FieldC], [FieldD],
[FieldE])
 
Thanks for the responses. The nature of your answers areonly furthe
indication of how out of my depth I am with this.

John,

I understand min works down, not across. I was hoping I was wrong.

Thanks

Ric
 
Back
Top