[RESOLVED] Having a 'max' issue

[Pasvorto]

I have this code, which works: tmpBOL is incremented

'find the next available number
'=====================================
Set rsBOLHeader = New ADODB.Recordset
'=====================================
sql = "SELECT [BOL] FROM BOLHEADER ORDER BY [BOL]"
rsBOLHeader.Open sql, CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdText
If Not rsBOLHeader.BOF And Not rsBOLHeader.EOF Then
    rsBOLHeader.MoveLast
    tmpBOL = rsBOLHeader![bol] + 1
End If
rsBOLHeader.Close
Set rsBOLHeader = Nothing
'=========================

However, when I do this, tmpBOL is always 1.

'find the next available number
Dim lastbol As Long
'=====================================
Set rsBOLHeader = New ADODB.Recordset
'=====================================
sql = "SELECT max([BOL]) as lastBOL FROM BOLHEADER "
rsBOLHeader.Open sql, CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdText
tmpBOL = lastbol + 1 
rsBOLHeader.Close
Set rsBOLHeader = Nothing
'=========================

Obviously a syntax error, but my documentation doesn't clear it up for me.

I'm trying to 'optimize' some code by doing away with unecessary loops.