"Data type mismatch in criteria expression" in SQL statement

[Skate Bart]

hey there,

Here's my complete code for a delete button that deletes a record in a Heirachical Flex Grid.

  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 :

cn.Execute strSQL

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.

[ChrisPadgham]

if [Tour Id] a number, if it is you should not have the single quotes in the the expression

strSQL = "Delete From Tour Where [Tour ID] = '" & fgdTour.TextMatrix(fgdTour.Row, 1) & "'"

ie

strSQL = "Delete From Tour Where [Tour ID] = " & fgdTour.TextMatrix(fgdTour.Row, 1) 

[QVeen72]

Hi,

Yes, as Chris above said, If [Tour ID] is a numeric field, then you should not wrap it with single quotes....

strSQL = "Delete From Tour Where [Tour ID]=" & Val(fgdTour.TextMatrix(fgdTour.Row,1))

Regards
Veena

[hemant3080]

Hi,

Please let me know what exactly are u doing as Chris and veena are 100% correct but i didn't get how it's working for 2 forms and not working on other 2 forms.

[Skate Bart]

Chris and Veena are both correct and the solution they provided worked great!
sorry I couldn't reply as I was busy with the other parts of code in the project.
The first two forms had a alphanumeric field as the first row in the fgd. But the other fields in the two forms were two numeric fields. Ayways, the solution worked flawlessly.!!!

thnx a bunch for it!!! =)

Cheers,
Skate Bart