determining maximum field value in recordset

[slames]

Hello, I'm hoping that someone will be able to help me with this. I am using an Access 2000 mdb and writing a module to carry out some analysis. I normally obtain the maximum field value of a query using dmax()but am unable to do so in this circumstance as I am creating the sql on the fly using variables. The sql string forms ok and does not error, but when trying to refer to this string in a dmax() situation - it does not like it.

I have a recordset using a DAO connection and am trying to find a way of obtaining the maximum value of one particular field but am failing, using syntax such as maxRecord = rs!max(fieldname). Is there a way of achieving this using the recordset object? If anyone could enlighten me on this I would be extremely grateful.

Many Thanks

Steph

 

[PHV]

Can you please post the SQL you create on the fly ?
The SQL version of DMax("field","domain","criteria") is:
SELECT ...
,(SELECT Max(field) FROM domain WHERE criteria) AS MaxOfField ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[slames]

Yes, of course - thank you - I wasn't thinking of it like that, I know get th evalue returned - cheers!