IIf expression help needed for Max(Field Name)

Max is an Aggregate function that returns the maximum value in a single column/field.  To find a maximum value from several fields in a single record, you can create your own function along these lines:

Public Function MaxOfList(ParamArray vValues() As Variant) As Variant

Dim vX As Variant

    MaxOfList = vVaues(0)

    For Each vX In vValues

        If vX >= Nz(MaxOfList, vX) Then MaxOfList = vX

    Next vX

End Function

And use it this way:

   MaxOfList(Field1, Field2, ...)

Or you can use a long series of nested IIf functions that is very difficult to read.

   IIf(field1>field2, IIf(field1>field3, IIf(field1>field4, field1, IIf(field2>field3, IIf(field2>field4, field2, IIf(field3>field4, field3, field4))))))

But I have no faith that I got all that glop right.

You're welcome, but no, that function is not "brilliant".  I have used that function exactly once in 17 years of using Access.  There's a very good reason why that function is not built into Access.  It's because it is only useful when you are violating the relational database rules of normalization, and violating the rules can lead to a cess pool of problems.

Hi that worked perfect! I couldn't get my head around the code and where the code would see the values that are in the table (not query! Sorry) I appreiciate you making it easy for me to understand.

Brilliant!

Jo

You could probably get the nested IIf expression to work if you could get all the paranthesis in the right place.  But it's a mess and not worth pursuing.

Open a new standard Module (not a class module), then Copy/Paste the function procedure into the module.  Use the Debug - Compile menu item to compile the code.  If there are no compile errors, save the module with a name like UtilityProcs.

Then open your query and put:

   MaxField:  MaxOfList(Field1, Field2, Field3, Field4)

in the Field row of a blank column to create a calculated field.  Run the query and you should see the highest value in the MaxField column.

Select type queries only retrieve records, they do not store anything anywhere so "will these values be stored in the query?" doesn't make much sense.

The fact that you are asking about something that compares values in different fields in a record is a strong indicator that you have not properly normalized (google it) your tables.  This question about the max of 4 fields is just the beginning of your troubles because it appears you are thinking like a spreadsheet and that just won't fly in a database.

Hi thanks for yr reply,

I tried this first:

IIf(field1>field2, IIf(field1>field3, IIf(field1>field4, field1, IIf(field2>field3, IIf(field2>field4, field2, IIf(field3>field4, field3, field4))))))

But it only leaves a value if the first field is the highest value, for the other fields it a blank?

I'm not very good with code but willing to try with help? Where would I put the code? on an Event? and will these values be stored in the query?

Where you say: "And use it this way" MaxOfList(Field1, Field2, ...) in which part of the code do I add this?

Public Function MaxOfList(ParamArray vValues() As Variant) As Variant

Dim vX As Variant

    MaxOfList = vVaues(0)

    For Each vX In vValues

        If vX >= Nz(MaxOfList, vX) Then MaxOfList = vX

    Next vX

End Function

Thanks

Jo