How to use a variable in q query?

[George]

Good Day,


In my Vba query when a use a literal number 207 the expected results is obtained; however when I replace the 207 with a declared variable "Number" an error message "too few parameters. Expected 1" occurs.

Can anyone explain this phenomenon?

Public Sub SumSalesAccounts()
    Dim curDatabase As Database
    Dim rst As Recordset
    Dim strSql As String
    Dim Number As Integer
    
    Number = 207
    
    strSql = "SELECT Sum(Quantity) AS SumQty, Account, Sum([Extended Price]) AS SumPrice, code3 " _
& "FROM [Chart of Accounts] INNER JOIN [Order Details Extended] ON [Chart of Accounts].[Account Name] = [Order Details Extended].Account " _
& "WHERE [Order ID]= 207  GROUP BY Account, [Order ID], code3;"
  Debug.Print strSql
    
    Set curDatabase = CurrentDb
    Set rst = curDatabase.OpenRecordset(strSql)
    Do While (Not rst.EOF)
     MsgBox rst.Fields(0) & " " & rst.Fields(1) & " " & rst.Fields(2)
     rst.MoveNext
    Loop
  
   Set rst = Nothing
   Set curDatabase = Nothing
   
End Sub

[June7]

You need to reference a control on a form or a popup InputBox will allow user to enter a value.

Number is a reserved word. Should not use reserved words as names for anything. Use something like intNum.

Concatenate variables.

& "WHERE [Order ID]= " & intNum & " GROUP BY Account, [Order ID], code3;"

[George]

Thanks. That was quick. It works fine.