Expected Expresssion Error when trying to run a function

[Zedster]

I have a function that uses a query to a SQL datatase to determine the quantity of an item that has been reserved. The code will not run, if I use the immediate window and type ?getqtyreserved('I_000012') I get an error. "Compile Error: Expected: Expression". I have tried adding breakpoints in the code, but I get the error before the breakpoints are reached.

I have no clue where to go from here and would appreciate any help.

Public Function GetQtyReserved(strPartNo As String) As Double

10    On Error GoTo err_GetQtyReserved

          Dim qdf As dao.QueryDef
          Dim db As Database
          Dim rst As dao.Recordset
          Dim strSQL As String
20        Dim dblOutput As Double: dblOutput = 0
          Dim strConnection As String
30        strConnection = "ODBC;DRIVER=SQL Server;SERVER=ZAB-SVR-01\ZABAPPS;DATABASE=Stores;Trusted_Connection=Yes"
         
40        strSQL = "SELECT [I_PtNo], sum([SM_Qty]) as QR FROM [dbo].[tblItemRequest] GROUP BY [I_PtNo] HAVING [I_PtNo] = '" & strPartNo & "'"
50        Debug.Print strSQL
         
60        Set db = CurrentDb
70        Set qdf = db.CreateQueryDef("")
80        qdf.Connect = strConnection
90        qdf.SQL = strSQL
100       qdf.ReturnsRecords = True
110       Set rst = qdf.OpenRecordset
         
120       With rst
130           .MoveFirst
140           Do Until .EOF
150               Debug.Print !QR
160               dblOutput = !QR
170           .MoveNext
180           Loop
190       End With
         
200       rst.Close
210       db.Close
220       Set rst = Nothing
230       Set qdf = Nothing
240       Set db = Nothing
         
250       GetQtyReserved = dblOutput

exit_GetQtyReserved:
260       Exit Function
         
err_GetQtyReserved:

270       MsgBox Err.Number & " " & Err.Description & vbCr & vbCr & "Error Line: " & Erl
280       Call Logger("Error", "GetQtyReserved," & Erl, Err.Number, Err.Description)
290       Resume exit_GetQtyReserved

End Function

 

[Guus2005]

Add line "291 Resume"

When the error occures drag the yellow right pointing arrow to this line and press F8.
That is the line where the error happens.

My guess is that !QR produces the error. I would use .Fields("QR") but that is because i dont use !

Or you can try to set the breakpoint at the beginning of the function and walk through it step by step using F8.

BTW: A compile error can be found by just compiling your code...
BTW2: Compiled your code. Nothing found.

HTH

 

[cheekybuddha]

My guess is that you have used single quotes to delimit a VBA string (in the Immediate Window).

Try:

?getqtyreserved("I_000012")

hth,

d

 

[Zedster]

Add line "291 Resume"

When the error occures drag the yellow right pointing arrow to this line and press F8.
That is the line where the error happens.

My guess is that !QR produces the error. I would use .Fields("QR") but that is because i dont use !

Or you can try to set the breakpoint at the beginning of the function and walk through it step by step using F8.

BTW: A compile error can be found by just compiling your code...
BTW2: Compiled your code. Nothing found.

HTH

Thanks for your input. The code compiles fine and I already tried adding breakpoint, the function does not even execute so even the first breakpoint is not reached. Cheekybuddha has guessed right.

 

[Zedster]

My guess is that you have used single quotes to delimit a VBA string (in the Immediate Window).

Try:

?getqtyreserved("I_000012")

hth,

d

Thanks Cheekybuddha, I had indeed been using single quotes, constantly switching from SSMS to VBA has scambled my brain, well worked out. It now executes, there is still a problem with the code as I am getting an ODBC call error on line 110. but at least I now know where to look.

 

[cheekybuddha]

If you plan to use a lot of pass-thru queries you can create a more generic wrapper along the lines of:

Function PTQuery(strSQL As String, blReturnsRecords As Boolean, Optional strCn As String) As DAO.Recordset

  Const CN_STRING As String = "ODBC;DRIVER=SQL Server;SERVER=ZAB-SVR-01\ZABAPPS;DATABASE=Stores;Trusted_Connection=Yes"

  With CurrentDb.QueryDefs.CreateQueryDef(vbNullString)
    .Connect = IIf(Len(strCn), strCn, CN_STRING )
    .SQL = .strSQL
    .ReturnsRecords = blReturnsRecords
    If blReturnsRecords Then
      Set PTQuery = .OpenRecordset
    Else
      .Execute dbFailOnError
    End If
  End With

End Function

Alternatively create a couple of deidcated stored pass-thru queries with the connection string already saved, one for returning records and the other for executing, and use functions to change their SQL and .OpenRocordset/.Execute

hth,

d

[NB Edited typo CN_CONNECT -> CN_STRING]

 

[cheekybuddha]

We replied at the same time there.

Any more info on the ODBC error? The SQL looks OK (though, I would use a WHERE clause rather than a HAVING clause) - perhaps an issue with the connection string?

 

[Zedster]

If you plan to use a lot of pass-thru queries

I use tons, I exclusively use unbound forms for both displaying data and editing/inputting data. The database contains no tables at all all data access to the SQL server is done with PTQ. So I will definitely give your suggestion a try.

 

[cheekybuddha]

Any more info on your ODBC error?

 

[Zedster]

I would use a WHERE clause rather than a HAVING clause

I believe HAVING is necessary on an aggregate query WHERE throws an incorrect syntax error in SSMS.

Any more info on the ODBC error?

I have solved the ODBC Error (it isn't the best error message tbh) and it is my bad. I have just returned to work after 2 weeks of illness and I have had a mare of a 48hrs, it is like everything I have learned on Access and VBA over the last 20 years has been for nothing, pretty much every piece of code I have written has failed to run and I am making repeated stupid errors. The ODBC error was due to me mis-spelling the name of a field in the query which I found by cutting and pasting the debug.print line into SSMS. Sorry for wasting so many of your time and thanks for all the help.

 

[cheekybuddha]

>> I believe HAVING is necessary on an aggregate query WHERE throws an incorrect syntax error in SSMS. <<
Are you sure? Yes, if the condition relies on the aggregation, but your's is just limiting the intital records returned.
Try in SSMS with:

SELECT 
  I_PtNo, 
  SUM(SM_Qty) as QR 
FROM dbo.tblItemRequest]
WHERE I_PtNo = 'ABC123'    -- <-- Insert a valid I_PtNo here
GROUP BY I_PtNo
;

>> I have solved the ODBC Error <<


d

 

[Zedster]

>> I believe HAVING is necessary on an aggregate query WHERE throws an incorrect syntax error in SSMS. <<
Are you sure? Yes, if the condition relies on the aggregation, but your's is just limiting the intital records returned.
Try in SSMS with:

SELECT
  I_PtNo,
  SUM(SM_Qty) as QR
FROM dbo.tblItemRequest]
WHERE I_PtNo = 'ABC123'    -- <-- Insert a valid I_PtNo here
GROUP BY I_PtNo
;

>> I have solved the ODBC Error <<


d

error

It can be either depending upon the ordering of the "GROUP BY", if group by is written after FROM then it needs to be HAVING, WHERE throws an incorrect syntax error. Either of the below work fine in SSMS or Access:

SELECT [I_PtNo], sum([SM_Qty]) as QR
FROM [dbo].[tblItemRequest]
GROUP BY [I_PtNo]
HAVING [I_PtNo] = 'I_000012'
GO

SELECT [I_PtNo], sum([SM_Qty]) as QR
FROM [dbo].[tblItemRequest] 
WHERE [I_PtNo] = 'I_000012'
GROUP BY [I_PtNo]
GO

The OBDC error was being caused by me misspelling SM_Qty which was preventing the query from running and Access throws an ODBC error if the SQL is bad.

 

[cheekybuddha]

I did my favourite trick of not checking what I am writing and left a stray square bracket in the SQL I posted:
So, does this not work in SSMS:

SELECT
  I_PtNo,
  SUM(SM_Qty) as QR
FROM dbo.tblItemRequest
WHERE I_PtNo = 'I_000012'
GROUP BY I_PtNo
;

?

 

[cheekybuddha]

If you are limiting your records returned by a constant value not calculated in the aggregation it is more efficient to use WHERE.

However, if you want to base the limiting on the aggregated value you must use HAVING:

SELECT
  I_PtNo,
  SUM(SM_Qty) as QR
FROM dbo.tblItemRequest
WHERE I_PtNo = 'I_000012'
GROUP BY I_PtNo
HAVING SUM(SM_Qty) > 500
;

WHERE comes before GROUP BY, HAVING comes after.

If you use the Access query builder it will always put the condition in HAVING, because it is lazy and stupid.