0

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 ?

Parfait
  • 104,375
  • 17
  • 94
  • 125
ali Ali
  • 1
  • 3
    Use `&` to concatenate, not `+`. – BigBen Dec 30 '19 at 19:53
  • 2
    Also, you could look at using parameters also https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters – Nathan_Sav Dec 30 '19 at 19:55
  • 3
    Your query is trying to compare a number field type to a string. Remove the single quotes. – HunterX3 Dec 30 '19 at 19:55
  • 2
    To avoid [SQL injection](https://xkcd.com/327/) (which may not work in Access but can if you later switch backends to Access app), consider parameterization as @Nathan_Sav. This is a good practice beyond VBA and beyond Access. – Parfait Dec 30 '19 at 21:23

1 Answers1

1

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

hc_dev
  • 8,389
  • 1
  • 26
  • 38
  • 1
    Thanks to [HunterX3: for setting the main focus](https://stackoverflow.com/questions/59536138/how-to-write-a-query-that-uses-a-number-as-parameter-and-number-type-field/59536495#comment105241902_59536138), [BigBen: for the concat-hint](https://stackoverflow.com/questions/59536138/how-to-write-a-query-that-uses-a-number-as-parameter-and-number-type-field/59536495#comment105241842_59536138), [Nathan_Sav: for parameterized-queries](https://stackoverflow.com/questions/59536138/how-to-write-a-query-that-uses-a-number-as-parameter-and-number-type-field/59536495#comment105241897_59536138). – hc_dev Dec 30 '19 at 23:11