Select Max Record with ADO

[Scott]

I'm trying to get the MAX() date in a field using an ADO call. I'm getting
the below ERROR. The field and table name are correct. I even tried other
fields from this and other tables. Am I using the wrong CursorType?


ERROR **********
Item cannot be found in the collection corresponding to the requested
name or ordinal

CODE ***********

Public Function GetMaxDataDate()

Dim objRS As ADODB.Recordset, sSQL As String

Set objRS = New ADODB.Recordset

objRS.CursorType = adOpenDynamic
objRS.LockType = adLockOptimistic
sSQL = "SELECT MAX(prod_date) FROM tblDate"
objRS.Open sSQL, CurrentProject.Connection

GetMaxDataDate = objRS!prod_date
Debug.Print objRS!prod_date
objRS.close
Set objRS = Nothing

End Function

 

[RoyVidar]

I'm trying to get the MAX() date in a field using an ADO call. I'm
getting the below ERROR. The field and table name are correct. I even
tried other fields from this and other tables. Am I using the wrong
CursorType?


ERROR **********
Item cannot be found in the collection corresponding to the
requested name or ordinal

CODE ***********

Public Function GetMaxDataDate()

Dim objRS As ADODB.Recordset, sSQL As String

Set objRS = New ADODB.Recordset

objRS.CursorType = adOpenDynamic
objRS.LockType = adLockOptimistic
sSQL = "SELECT MAX(prod_date) FROM tblDate"
objRS.Open sSQL, CurrentProject.Connection

GetMaxDataDate = objRS!prod_date
Debug.Print objRS!prod_date
objRS.close
Set objRS = Nothing

End Function

Either try aliasing

sSQL = "SELECT MAX(prod_date) as MyMaxDate FROM tblDate"
....
Debug.Print objRS!MyMaxDate

or just use the first field of the recordset

objRS.Fields(0).value

 

[Scott]

that did it, thanks.

Either try aliasing

sSQL = "SELECT MAX(prod_date) as MyMaxDate FROM tblDate"
...
Debug.Print objRS!MyMaxDate

or just use the first field of the recordset

objRS.Fields(0).value