| re: given an sql select query, how to get the data
On 18 May 2005 04:22:47 -0700, "enrio@online.no" <enrio@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.
[color=blue]
>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[/color]
| 
