Data Type Mismatch??

[Sheffield]

I'm having difficulty with a SQL statement that is attempting to retreive Integer data from a database field that matches the Text property of a DataCombo box. I was thinking that all I had to do was convert the DataCombo.Text to an Integer, but that didn't work but I'm receiving the the following error: "-2147217913 Data type mismatch in criteria expression."

Can anyone help me on this?? Here is my code.



Option Explicit

Private cn As New ADODB.Connection
Private rs As New ADODB.Recordset


Private Sub dcboDNIS_Change()
On Error GoTo err_dcboDNIS_Change

Dim intDNIS As Integer
Dim strSQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = App.Path & "\TEST.mdb"
cn.Open

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic

'Convert the dcboDNIS.Text into an Integer to avoid the
' data type mismatch. (Doesn't work correctly)
intDNIS = CInt(dcboDNIS.Text)

'A recordset containing the names of all the columns
strSQL = "SELECT tbl800.Client, tbl800.DNIS " & _
"FROM tbl800 " & _
"WHERE tbl800.DNIS = '" & intDNIS & "'"
rs.Open strSQL, cn

 

[woyler]

What is the value of dcboDNIS.Text? If it is numeric then:

strSQL = "SELECT tbl800.Client, tbl800.DNIS " & _
"FROM tbl800 " & _
"WHERE tbl800.DNIS =" & intDNIS

Check the last line. You were surroundind a numeric value with "'"

 

[Sheffield]

Wow, do I feel stupid!

Your suggestion worked perfectly.

Many thanks!