-
June 27th, 2012, 06:48 PM
#1
"Data type mismatch in criteria expression" in SQL statement
hey there,
Here's my complete code for a delete button that deletes a record in a Hierarchical Flex Grid.
Code:
Dim rsTour As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim strSQL As String
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & App.Path & "\Luckshan Tours & Travels.mdb;" & _
"Persist Security Info:False"
cn.Open
msg = MsgBox("Delete Record?", vbYesNo)
If msg = vbYes Then
strSQL = "Delete From Tour Where [Tour ID] = '" & fgdTour.TextMatrix(fgdTour.Row, 1) & "'"
cn.Execute strSQL
strSQL = "Select * From Tour"
rsTour.Open strSQL, cn, adOpenStatic, adLockPessimistic
Set fgdTour.DataSource = rsTour
End If
Set fgdTour.DataSource = rsTour
If rsTour.RecordCount <> 0 Then
rsTour.Update
End If
The code works great for two of my forms, but for the other two it doesn't. It gives me an error saying "Data type mismatch in criteria expression" and highlights the statement :
Please help.... I've gone over and over the code for errors but could find none, I mean it works perfectly for the other two forms.
Last edited by WizBang; June 28th, 2012 at 03:03 PM.
Reason: Formatted code with indentation for clarity
-
June 28th, 2012, 03:11 PM
#2
Re: "Data type mismatch in criteria expression" in SQL statement
Try adding a line immediately after defining the strSQL string (just above the execute line) to print the string to the Immediate/Debug window.
Does the code reside in a module for each form/button to access, or does each button have a duplicate copy of the code?
Please remember to rate the posts and threads that you find useful.
How can something be both new and improved at the same time?
-
June 29th, 2012, 07:53 AM
#3
Re: "Data type mismatch in criteria expression" in SQL statement
Spontaneaously I'd suppose, the [Tour ID] field being numeric, probably being an index key, too.
Therefore your SQL statement should rather be
Code:
strSQL = "Delete From Tour Where [Tour ID] = " & Val(fgdTour.TextMatrix(fgdTour.Row, 1))
No quotes and using the Val function because TextMatrix() property always returns a string.
Hm... come to think of it...
This should work, too:
Code:
strSQL = "Delete From Tour Where [Tour ID] = " & fgdTour.TextMatrix(fgdTour.Row, 1)
Simply leave away the single quotes which male SQL think the argument is of type string.
It works since we build an sQL-string and append the numeric value in form of string characters. The & operator when using Cal() anyway converts to a string when appending to other characters.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|