Error 3021, no current record has been found, Query data from Access to Excel

I'm writing a function in Excel VBA which will be called to extract data from an Access database, I'm using ADO connection. The function Get_g_gtop has parameters defined as bellow. Now, I try to use a command object to get the value from recordset, however, I got the error message 3021 : Either BOF or EOF is true, or current record has been deleted. Requested operations requires a current record. The debug points to the line : Get_g_gtop = rst.Fields(0).Value.

Is there anything wrong with the SQL statement to query in Access? Any advice would be highly appreciate!

Bing

Function Get_g_gtop(ByVal VehType As String, ByVal Speed As Single) As Variant

Dim Dbfilepath As String

Dbfilepath = "C:\Users\sevenice\Desktop\EM Database.accdb"

Set cnn = New ADODB.Connection

cnn.Open "Provider= Microsoft.ACE.OLEDB.12.0;" & " Data Source=" & Dbfilepath & ";" & "Persist Security Info =False;"

'Set rst = New ADODB.Recordset

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn

'Dim QueryStr As String
Dim S As Single

If StrComp(VehType, "LDV") * StrComp(VehType, "LDT") * StrComp(VehType, "LHD<=14K") * StrComp(VehType, "LHD<=19.5K") = 0 Then
   S = 35.6
   'QueryStr = "SELECT [g/gtop] FROM [EM Database].[N (t) Data] WHERE [Vehicle Category]= "" & VehType & "" AND S = 35.6 " & " AND [Speed Lower] <= " & Speed & " AND [Speed Upper] >= " & Speed & ";"

   cmd.CommandText = "SELECT [g/gtop] FROM [EM Database].[N (t) Data] WHERE [Vehicle Category]= "" &  VehType & "" AND S = 35.6 " & " AND [Speed Lower] <= " & Speed & " AND [Speed Upper] >= " & Speed & ";"
   'rst.Open QueryStr, cnn

   Set rst = cmd.Execute

   Get_g_gtop = rst.Fields(0).Value

ElseIf StrComp(VehType, "MHD") * StrComp(VehType, "HHD") * StrComp(Veh开发者_高级运维Type, "Urban Bus") = 0 Then
   S = 26.7
   QueryStr = "SELECT [g/gtop] FROM [EM Database].[N (t) Data] WHERE [Vehicle Category]=" & VehType & " AND S = 26.7 " & " AND [Speed Lower] <= " & Speed & " AND [Speed Upper] >=" & Speed & ";"
   rst.Open QueryStr, cnn
   Get_g_gtop = rst.Fields(0).Value

End If

End Function

After you open the recordset (Set rst = cmd.Execute) you will have to check whether it contains any data, before you try to access that data, for example:

if not rst.EOF then
'do your stuff with the data
end if

Reference on w3schools.com

The error you are receiving indicates that you are not geting any records from your SELECT-Statement. Check it as suggested by HansUp.