Find Max of many fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with 25 different text boxes that are all formatted to
mm/dd/yyyy. I'm trying to write a query that find the most recent (maximum)
of them. I started by having the query pull the values for all 25 boxes from
the table where they are stored. I thought that I would be able to just
write an expression in another column of the query that finds the max of them
all. What I wrote is:

Current: Max("[Field1]","[Field2]", ..."[Field25]")

Access complained that the expression I was entering had a function
containing the wrong number of arguments. I tried the same formula without
the parentheses and again without the brackets, but to no avail. Is my
syntax wrong somehow? Or am I going about this all wrong? Thanks in advance
for any help.
 
The max function is an aggregate function that can only be performed on the
values in a single column of a table or query. Max is looking for exactly 1
argument.

Are these dates all in the same column in the table? If so, you just do
max([ColumnName])
 
I have been trying to do the same thing, can you tell me if this worked for
you and if so, how did you accomplish it.
 
I have been trying to do the same thing, can you tell me if this worked for
you and if so, how did you accomplish it.

Tim said:
I have a form with 25 different text boxes that are all formatted to
mm/dd/yyyy. I'm trying to write a query that find the most recent (maximum)
of them. I started by having the query pull the values for all 25 boxes from
the table where they are stored. I thought that I would be able to just
write an expression in another column of the query that finds the max of them
all. What I wrote is:

Current: Max("[Field1]","[Field2]", ..."[Field25]")

Access complained that the expression I was entering had a function
containing the wrong number of arguments. I tried the same formula without
the parentheses and again without the brackets, but to no avail. Is my
syntax wrong somehow? Or am I going about this all wrong? Thanks in advance
for any help.
Click to expand...
Click to expand...

You are going about this all wrong.

"Fields are expensive, records are cheap". If you have a one (record)
to 25 (dates) relationship, the proper structure would be to use two
tables, in a one to many relationship; rather than one date per FIELD
you would have one date per RECORD. It then becomes a very simple
Totals query to find the maximum value of Datefield across two, or
twenty-five, or 31812 records.

Access is not a spreadsheet. It's a relational database! Use it
relationally, and you'll find that it's much simpler.


John W. Vinson[MVP]
 
I was having the same issue, I tried this Function that Ken Snell recommended
and it works great -

"Here is a generic function that will accept any number of values and return
the maximum of them:


Public Function MaxValueVariantArray(ParamArray ValuesArray() As Variant) As
Variant

' ** THIS FUNCTION RETURNS THE MAXIMUM VALUE FROM AN ARRAY OF VALUES.
' Ken Snell 19 May 2005

Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB >= 0 And xlngLB >= 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) > xvarTp Then xvarTp = ValuesArray(xlngCnt)
Next xlngCnt
End If
MaxValueVariantArray = xvarTp
End Function
 
Back
Top