CODEWITHSUNDEEP
sqlvbams-access
ali Ali
ali Ali

Reputation: 1

How to write a query that uses a number as parameter and number type field?

I need to start a query to retrieve data from Access database using VBA which I want to use a variable number as a parameter. Is it possible?

like the: 

field name: NMT field type (number)
table name: Orders

and the code is like the following:

Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim X as Integer
X = me.textbox1.value
Con.Open "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" & U.Database01 & "\DB.accdb;Persist Security Info=False"
Rs.Open "select * from Orders where nmt = '" + X + "'", Con, adOpenDynamic, adLockPessimistic

Whenever I run this query, I get a run-time error '13' type mismatch.

Any suggestions ?

Upvotes: 0

Views: 991

Answers (1)

hc_dev
hc_dev

Reputation: 9418

Multiple Issues

  1. Type-mismatch in WHERE clause: Your query (i.e. the WHERE clause) tries to compare a Number-column from database with a String-value (e.g. WHERE numberField = '123'). This will result in a runtime error Type mismatch (Error 13). See also similar question.

  2. Unsafe to use + to concatenate Strings When building the query you tried to concatenate the query-template with the number-parameter by a plus-sign. This works only when operating on numbers. See related question

Solution

  1. remove single-quotes: you should compare the Number-column NMT with a number literal (e.g. WHERE nmt = 123)
  2. use & to concatenate strings. This will also convert numbers to strings. Besides I explicitly used CStr function below.
Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim strSQL As String
Dim nmtNumber as Integer ' you named it x before

nmtNumber = me.textbox1.value
strSQL = "SELECT * FROM Orders WHERE nmt = " & CStr(nmtNumber) ' removed single-quotes and used ampersand to concatenate with converted string
Con.Open "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" & U.Database01 & "\DB.accdb;Persist Security Info=False"
RS.Open strSQL, Con, adOpenDynamic, adLockPessimistic

Further improvement

I already extracted the SQL string (building) into a separate variable strSQL above.

Better would be to use predefined/prepared and parameterized queries:

See also

Upvotes: 1

Related Questions