I'm trying to make an Excel formula work in ACCESS 2007.
the excel formula is =MIN(2,SUM(N5:P5))
I get an error when I put this into a text box in my form:
=MIN (2,SUM([EMPLOYMENT]+[FINANCIAL]+[EDUCATION]))

expression has a function with the wrong number of arguments.

Anyone see something obvious?

---

Reply from Richard Rost:

Francis, the MIN function behaves differently in Access than it does in Excel. In Excel, for example, your statement of

=MIN(2,SUM(N5:P5))

Says "give me either the SUM of the range N5:P5, or the number 2, whichever is lower."

The MIN function doesn't work that way in Access. You can only use MIN in a text box in the FOOTER of a form to show you the lowest value of a set of records on a continuous form (all of the records, same field). For example:

=MIN(ProductCost)

This will show you the lowest product cost of all of the listed product costs. It does NOT take multiple parameters like you're trying to give it, hence the error.

What you need to handle this problem in Access is an IIF function. Something like this should work:

=IIF((Employment+Financial+Education)<2, (Employment+Financial+Education), 2)

This says "If the sum of those three fields is less than 2, then give me that value, otherwise give me 2."

It should accomplish the same thing. Let me know if this works. I'll be covering the MIN function in one of my next classes.