MS Access - SQL/VBA SELECT SUM - Can not execute a select query #ERROR-CodePudding

I'm trying to total up the amount of hours lead time on a job card to be assigned to a variable so it can be used later in a job scheduling code.. but getting a run time error and I don't know why

SUM of OpleadTm filtered by job number (sounds so simple!)

Private Sub Command50_Click()

Dim db As DAO.Database
Dim strSQL As String
Dim ActvJbNum As Long
Dim pd As Long


ActvJbNum = Me.EntJobNum
pd = SumOfOpleadTm

Set db = CurrentDb

strSQL = "SELECT Sum(tblRouting.OpleadTm) AS SumOfOpleadTm, tblRouting.[Job Number] " & vbCrLf & _
"FROM tblRouting " & vbCrLf & _
"GROUP BY tblRouting.[Job Number] " & vbCrLf & _
"HAVING(tblRouting.[Job Number])= """ & EntJobNum & """"

db.Execute strSQL

MsgBox ("pd")

End Sub

I keep getting a run time error saying 'Cannot execute a select query' any idea whats causing it?

CodePudding user response：

Your logic of assigning pd as the resultant value of query is flawed...

Instead of using CurrentDb, you can use the following (assuming you have corrected your SQL):

pd = CurrentProject.Connection.Execute(strSQL).Fields("SumOfOpleadTm")