Data type mismatch in criteria expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Data type mismatch in criteria expression

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-03 : 22:19:11

Data type mismatch in criteria expression
Access 2002 (Service Pack 1) and SQL 2000 Server

Access 2002 Module1
Public Public_Department_Name As String
Public Public_SO_Number As Long
Public Public_Item_Number As String

SQL 2000 Server
Table Tbl_SWBD_TR
Column Name Data Type Length Allow Nulls
Department_Name nvarchar 50 YES
SONumber int 4 YES
ItemNumber nvarchar 50 YES

////////////////////////////////////////////////////////

Private Sub Form_Load()
Dim Str_SQL As String
Dim Number_Of_Records As Integer
Dim Recordset_SWBD As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim cnThisConnect As ADODB.Connection

Set cnThisConnect = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

Str_SQL = "SELECT * FROM Tbl_SWBD_TR" _
& " WHERE Tbl_SWBD_TR.Department_Name = '" & Public_Department_Name & "' AND" _
& " Tbl_SWBD_TR.SONumber = '" & Public_SO_Number & "' AND" _
& " Tbl_SWBD_TR.ItemNumber = '" & Public_Item_Number & "';"

'ERROR: Data type mismatch in criteria expression
'Yellow Highlight is the code directly below
Recordset_SWBD.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

Number_Of_Records = Recordset_SWBD.RecordCount
If Number_Of_Records = 0 Then
Me!Department_Name = Public_Department_Name
Me!Item_Number = Public_Item_Number
Me!SO_Number = Public_SO_Number
End If

End Sub

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 00:46:16

If SoNumber or ItemNumber is numeric, drop the ' around the values.

Peter Larsson
Helsingborg, Sweden