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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Data type mismatch in criteria expression
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lin100
Yak Posting Veteran

USA
70 Posts

Posted - 09/03/2006 :  22:19:11  Show Profile  Reply with Quote
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

Sweden
30421 Posts

Posted - 09/04/2006 :  00:46:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If SoNumber or ItemNumber is numeric, drop the ' around the values.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Lin100
Yak Posting Veteran

USA
70 Posts

Posted - 09/04/2006 :  10:50:51  Show Profile  Reply with Quote
Thank you Peter Larsson for helping me. I will try this and let you know.
It is so good to receive a respond.
Go to Top of Page

Lin100
Yak Posting Veteran

USA
70 Posts

Posted - 09/10/2006 :  13:26:58  Show Profile  Reply with Quote
Hi Peter Larsson. It does work. Thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000