On 18 May 2005 04:22:47 -0700, "en***@online.no" <en***@online.no>
wrote:
Option 1: Use the DMax function. 1-line solution, but perhaps not very
fast for large (> 1000 rows) data sets.
Option 2: Use VBA code with DAO to capture the results in a recordset,
then read the value into your variable:
dim db as dao.database
dim rs as dao.recordset
set db = currentdb()
set rs = db.OpenRecordset(Query, dbOpenSnapshot)
MaxSerial = rs(0)
rs.close ' etc.
Option 3: Use a stored query, with a parameter.
The query:
select max(serialnumber) from participants where startyear =
[parStartYear]
The code:
dim db as dao.database
dim rs as dao.recordset
dim qd as dao.querydefs
set db = currentdb()
set qd = db.querydefs("MyQuery")
qd!parStartYear = Longyear
set rs = qd.OpenRecordset(dbOpenSnapshot)
MaxSerial = rs(0)
rs.close ' etc.
-Tom.
I feel quite dumb having to ask this, but I cannot seem to find this in
the Microsft documentation pages.
In Access 2000, I create a string containing the sql query:
Dim MaxSerial As Integer
Dim Longyear As Integer
Longyear = year(Date)
Query = "select max(serialnumber) from participants " _
+ "where startyear = " + Str(Longyear)
I would like to have the result of this query in a basic variable.
MaxSerial = ????
I do not, in principle, want to create a form or window showing a table
of records, I just want to get a number in the variable, or NULL if
there is no row in 'participants' having non-null 'startyear'.
Thanks,
Enrique
