# MS Access question: Finding the minimum value in multiple fields

What expression can I write that will look at Fields A, B and C and return the lowest of those 3 values into Field D (using an update query)?

I’ve tried to use the Min function as Min(Field A, Field B, Field C), but it’s telling me “The expression you entered has a function containing the wrong number of arguments”. I have no idea what that means. Is Min not the expression that I should be using?

I suspect you can do it using a complicated If statement, but I’m too braindead to figure out how to write it in such a way as to reliably return the lowest of 3 values. Seems like there should be an easier way.

TIA

You were close.

The Min function needs a range. So if your values are in cells A1 through A3, the correct syntax is Min(A1:A3)

I’m trying to use the Min function in Access, though…not Excel. Is there something in Access that works a similar way?

You could try using a UNION query.
SELECT field1 FROM table
UNION
SELECT field2 FROM table
UNION
SELECT field3 FROM table

and order the results in ascending order. The low number will come out on top.

Zev Steinhardt

Min will probably do the trick, but it only takes two arguments, not three. What should work is Min(Min(Field A, Field B),Field C). It’s ugly, and I’m sure there’s a much more elegant way to do what you want, but I rarely use Access, and when I do, I always have a frontend using something like Visual Basic, so I can just grab all the values with a query, programatically find the smallest, and then insert that into a generated update statement.

As you’re using an update query to put the lowest value into field D, you could just do it with three queries instead, so

First:

UPDATE Table1 SET Table1.FieldD = [FieldA];

then

UPDATE Table1 SET Table1.FieldD = [FieldB] WHERE (((Table1.FieldD)>[FieldB]));

then

UPDATE Table1 SET Table1.FieldD = [FieldC] WHERE (((Table1.FieldD)>[FieldC]));

At each stage, FieldD will only be updated if the value being compared is lower than what is already in there

There’s got to be a better way though - one that does it in a single operation, but I’m scratching my head…

Mangetout, I wound up doing something similar…I created temp fields and did a few queries to compare A to B and B to C, then the results of those 2 resulting fields to each other. Seems to have worked, but yeah…it’s ugly, and it seems like there should be a better way.

I didn’t try JoeRandom’s suggestion about nesting the Min statements, but I might try it after I’m done with this project just for future reference.

There is a way of doing it with nested IIF (Immediate If) functions, but it isn’t going to be pretty; I’ll post back in a minute when I’ve tested it

OK, take a deep breath, sit down…

``````
UPDATE Table1 SET Table1.FieldD = IIf(((IIf([fieldA]<[fieldB],[fieldA],[fieldB])))<[fieldc],((IIf([fieldA]<[fieldB],[fieldA],[fieldB]))),[fieldc]);

``````

It works, honest! Here’s how:

Iif(<Expression>,<truepart>,<falsepart>) returns truepart if the expression is true and falsepart if it is false, so…

Iif([fieldA]<[FieldB],[fieldA],[fieldB]) returns the value of whichever out of A or B is lower - let’s call this little nugget of code X

Now we need another evaluation to check whether X is lower than FieldC and return the FieldC value or the X value, whichever is lower, so…
Iif(X<[fieldC],X,[fieldC]) - except that’s pseudocode and doesn’t work, so we replace X (both instances) with what it really is - the evaluation of FieldA and FieldB and we get:
IIf(((IIf([fieldA]<[fieldB],[fieldA],[fieldB])))<[fieldc],((IIf([fieldA]<[fieldB],[fieldA],[fieldB]))),[fieldc])

Or maybe it’s clearer this way:

IIf((([COLOR=Blue]IIf([fieldA]<[fieldB],[fieldA],[fieldB])))<[fieldc],((IIf([fieldA]<[fieldB],[fieldA],[fieldB]))),[fieldc])[/COLOR]

Then again, maybe not. It works.

Heh…you’re way more patient than I am. And yes, I think I follow what the logic in the code is, but I’ll be damned if I had the brainpower today to work it out.

Thanks!

Why doesn’t min(A,(min(B,C)) work? Looks fine to me, and far less complicated than that monstrosity Mangetout came up with… no doubt his solution would work, but why doesn’t nested min work?

Good Lord. Someone really needs to revoke my posting rights pre-coffee. Sorry for the mixup.

The Min function in an Access query only accepts one argument which is meant to be a column name. It is designed to find the lowest value in the column passed as the only argument.

You might be thinking of the Min function in Excel.

I have to say that in my opinion, the absence of a ‘scalar’, non-aggregating minimum function (and maximum function) is one of the most annoying shortcomings in access query language. I don’t think it would take much to come up with an alternate function name for it, and there are so many places where I would glady have used it rather than come up with complicated IIF constructions. (You could probably even set it up to take arbitrarily many arguments if you were feeling ambitious.)

::sighs::

Yeah…

On the other hand, you could argue that if your table is set up in such a way that such a comparison is meaningful, there’s probably an issue with your table design.

You can define your own functions quite easily. To solve your problem:

• create a new module, call it “UDF”
• paste the following:
``````

Public Function fMin1(arg1, arg2, Optional arg3) As Variant
Dim vMin As Variant

If arg1 < arg2 Then
vMin = arg2
Else
vMin = arg1
End If
If Not IsMissing(arg3) Then
If arg3 < vMin Then
vMin = arg3
End If
End If
fMin1 = vMin

End Function

Public Function fMin2(ParamArray varValues() As Variant) As Variant

Dim I As Integer, vMin As Variant
vMin = varValues(0)
For I = 1 To UBound(varValues())
If IsNull(vMin) Then
vMin = varValues(I)
ElseIf varValues(I) < vMin Then
vMin = varValues(I)
End If
Next I
fMin2 = vMin

End Function

``````

(I have given you two functions. fMin2 uses a “ParamArray” - I’m not sure when it was introduced into MS Access, so I also gave you fMin1 which will work in older versions. The advantage of fMin2 is that any number of values (including 1!) can be passed, whereas fMin1 requires either 2 or 3 values passed.)

So, next use your new User-Defined Function(s) in an expression:
Expr2: fmin2([field1],[field2],[field3])… returns the minimum of the three fields.

Note that fMin2 can handle nulls “properly” (they are ignored), fMin1 will need some work to handle nulls, I leave that up to you!

Of course, I screwed it up somehow :smack:

The first function should be:

``````
Public Function fMin1(arg1, arg2, Optional arg3) As Variant
Dim vMin As Variant

If arg1 < arg2 Then
vMin = arg1
Else
vMin = arg2
End If
If Not IsMissing(arg3) Then
If arg3 < vMin Then
vMin = arg3
End If
End If
fMin1 = vMin

End Function

``````

If Access language follows the conventions of SQL, then the MIN function is to find the lowest value in a database column, and what you’d use to get the lowest value in a list of values is LEAST.

Someone has to say it so I’ll do it…
Your data is not normalized. Your tables need to be redesigned and your data reformatted for a relational database.

Sorry for the hijack, carry on.