Query Syntax Problem

[Len]

I have never used MAX before and I cannot find an example of what I am doing
wrong.

Here is the code:

Set rstFederalClass = db.OpenRecordset("SELECT * MAX(TotPotPlyrs) FROM
FederalClass ORDER BY ST")

I also tried:

Set rstFederalClass = db.OpenRecordset("SELECT MAX(TotPotPlyrs), * FROM
FederalClass ORDER BY ST").

I need all of the fields in the record that is the maximum amount, but I do
not know where to put the asterisk (*).

Thanks in Advance

Len

 

[Len]

I also tried this and received a syntax error.

Set rstFederalClass = db.OpenRecordset("SELECT * FROM FederalClass WHERE
BestScore = (SELECT MAX(TotPotPlyrs) FROM FederalClass")

 

[John Spencer]

Well, you can't use the * (all fields) in a query where you are using an
aggregate function (such as Max). You MUST list the fields AND you must group
by any field that are not using an aggregate function.

So you would have something like the following for your string.

Dim StrSQL as String
StrSQL = "SELECT Max(TotPotPlyrs) as MaxTot, ST, FieldHeight, FieldDate" & _
" FROM FederalClass" & _
" GROUP BY ST, FieldHeight, FieldDate" & _
" ORDER BY ST"

Set rstFederalClass = db.OpenRecordset(strSQL)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County