Catching SQL query

[JayBeez]

Hi

I have a very simple SQL query like this:
strSQL = "select max(fieldname) from tablename"

'Open connection ...
'Excute query...
'Until here everything goes well ...
dim iMax as integer
'Error getting value
iMax = rs.Fields("fieldname").value

But it doesn't work. Anyone any idea?

Greetings,
Jay

[rocket]

Try this:

iMax = rs("fieldname")

[Ahara]

when using an aggregate function like MAX, even if it does not return an apparant result, it will return a row (in Oracle anyway), so you may be getting the error due to a null value

[Cimperiali]

iMax = rs.Fields(0).value
the recordset has a filed wich is not "fieldname"
unless you do something like
"select max(fieldname) as TheFieldDescription from tablename"
and only then you could do:
iMax = rs.Fields("TheFieldDescription").value
If table is empty, you should get a record in any case.
Do not know if null or 0 value (cannot test right now)...
If null you can do
iMax = iif(isnull(rs.Fields("TheFieldDescription").value), 0,rs.Fields("TheFieldDescription").value)

[Cimperiali]

You declared iMax as integer.
This means max value should not excede around 32000
If you could get a higher value, then declare it as long.