Nil exception when Getting max value SQLite

I use this, and if checking at line Break2 I can see that rMax(FieldName) keeps the highest value.
But somehow in running further I got a NilException when try

HighestValue= rMax.Field(“FieldName”).IntegerValue
HighestValue is an integer public property of the page

[code]
Dim rMax as RecordSet
dim rMax as RecordSet =db.SQLSelect(“select max(FieldName) from Table WHERE (Deleted = 0)”)
if db.Error then
break1
end if

Break2
rMax.movefirst
HighestValue= rMax.Field(“FieldName”).IntegerValue
rMax.Close[/code]

Solve it! SQL needs to be written…

db.SQLSelect(“select FieldName, max(FieldName) from Table WHERE (Deleted= 0)”)

I think you can change:

HighestValue= rMax.Field("FieldName").IntegerValue

to:

HighestValue= rMax.IdxField(1).IntegerValue

or use:

db.SQLSelect("select max(FieldName) AS max_fn from Table WHERE (Deleted = 0)")

then:

HighestValue= rMax.Field("max_fn").IntegerValue

Note: still learning, maybe I’m wrong. I’ll try later to do some tests.

[quote=406415:@Alberto De Poo]

or use:

db.SQLSelect("select max(FieldName) AS max_fn from Table WHERE (Deleted = 0)")

then:

HighestValue= rMax.Field("max_fn").IntegerValue

Yes, this is the best way, and I use it all the time.