CODEWITHSUNDEEP
databasevbams-accesswhere-clause
Pecurka
Pecurka

Reputation: 113

How to enter a parameter in a Where clause?

With this VBA code I get an error:

Too few parameters. Expected 1.

I do not know how to enter it right.

Dim rs_invoice As DAO.Recordset
Set rs_invoice = CurrentDb.OpenRecordset("SELECT * FROM order_tbl WHERE invoice_no Is Null AND company_name='" _
 & Me.cmb_start_company.Column(1) & "' AND shiped=" & "True")


If Not (rs_invoice.EOF And rs_invoice.BOF) Then
    rs_invoice.MoveFirst
    Do Until rs_invoice.EOF = True
        rs_invoice.Edit
        rs_invoice!invoice_no = invoice_number
        rs_invoice.Update

        rs_invoice.MoveNext
    Loop
Else
   'No records
End If

'Finish


rs_invoice.Close 'Close the recordset
Set rs_invoice = Nothing 'Clean up

Upvotes: 0

Views: 1790

Answers (2)

Vityata
Vityata

Reputation: 43595

Just guessing. Can you try this:

"SELECT * FROM order_tbl WHERE (invoice_no Is Null) AND (company_name='" _
 & Me.cmb_start_company.Column(1) & "' )AND (shiped=" & "True")

option 2:

dim str_test as string
str_test =     "SELECT * FROM order_tbl WHERE (invoice_no Is Null) AND (company_name='" & Me.cmb_start_company.Column(1) & "' )AND (shiped=" & "True")

debug.print str_test

What do you get in the immediate window?

option 3:

  "SELECT * FROM order_tbl WHERE (invoice_no Is Null) AND (company_name='" _
     & Me.cmb_start_company.Column(1) & "' )AND (shiped=True)"

option 4: Then use the sample for here and it should work. https://msdn.microsoft.com/en-us/library/bb243786(v=office.12).aspx Probably. Just make sure that you set your recordset the same way:

Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Dim strSQL As String

Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Products WHERE Discontinued = No " & _
         "ORDER BY ProductName"
Set rstProducts = dbsNorthwind.OpenRecordset(strSQL)

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

I'm not all that familiar with Access/DAO, but you're concatenating the parameter value into your query. With ADO your command text would look something like this instead:

Const sql As String = "SELECT * FROM order_tbl WHERE invoice_no Is Null AND company_name = ? AND shiped = True"

The ? is a placeholder for a parameter value; instead of calling OpenRecordset(sql) directly, you need to make a Command and add a proper Parameter.

Seems DAO handles this a bit differently, see Parameterized queries in Access - surely one of the answers is applicable here. Because I don't like random ad-hoc queries hitting my databases I'd probably go with the QueryDefs approach in this answer:

Dim qdf As Querydef
Dim rst As Recordset

'then we'll open up the query:
Set qdf = CurrentDB.QueryDefs(qryname)

'Now we'll assign values to the query using the parameters option:
qdf.Parameters(0) = qryStartDate
qdf.Parameters(1) = qryEndDate

'Now we'll convert the querydef to a recordset and run it
Set rst = qdf.OpenRecordset

That would be:

Dim query As QueryDef
Set query = CurrentDB.QueryDefs("TheQuery")

query.Parameters(0) = Me.cmb_start_company.Column(1)

Dim result As Recordset
Set result = query.OpenRecordset

Upvotes: 0

Related Questions