Data type mismatch is criteria expression Error Message

[psimpson]

The code below is placed in a button control in ms access 2007 (using vba), however the above error message is returned at the set rst = db.openrecordset(strSQL, OpenSynaset) line. I am struggling with this for some time, but no solution yet. Any help is appreciated.

Thanks.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strSQL As String
Dim strSQL1 As String

Set db = CurrentDb()
strSQL = "SELECT * FROM Table1 WHERE Table1.Project_Number=" & Me.PROJECT_NUMBER
Set rst = db.OpenRecordset(strSQL, OpenDynaSet)
If rst.RecordCount > 0 Then
strSQL1 = "SELECT * FROM Table2 WHERE Professional_REG_Number = " & rst(PROFESSIONAL_REG_NUMBER)
With rst1
If Not (.BOF And .EOF) Then
.Edit
.Delete
.Update
End If
.Close
End With
Set rst1 = Nothing
Else
'Do Nothing
End If
With rst
If Not (.BOF And .EOF) Then
.Edit
.Delete
.Update
End If
.Close
End With

Set rst = Nothing
'====================================ERROR HANDLING===========================================================

Exit_Delete_Record_s__Click:
Exit Sub

Err_Delete_Record_s__Click:
MsgBox Err.Description
Resume Exit_Delete_Record_s__Click
End Sub

 

[PNGBill]

Declare your project number variable first and use that in your strSQL.

Dim ProjectNumber As Long
ProjectNumber = Me.PROJECT_NUMBER

strSQL = "SELECT * FROM Table1 WHERE Table1.Project_Number=" & ProjectNumber

 

[pbaldy]

Try

strSQL = "SELECT * FROM Table1 WHERE Table1.Project_Number='" & Me.PROJECT_NUMBER & "'"

 

[psimpson]

That worked. Thanks so much to all who responded.

 

[PNGBill]

Good news but what worked ? My guess or Pauls experienced advice

 

[psimpson]

Actually Paul's solution worked best for my needs and all responses appreciated.

Thanks.

 

[pbaldy]

Happy to help. PNGBill, given the error message, your method was doomed from the start.

 

[PNGBill]

Thanks Paul, I assumed datatypemismatch was because either the data was different ie text/number or the code couldn't understand what it was ie hadn't been declared.

Your solution is really just correcting the code string which I guess means the code was "correct" just couldn't be read by vba ??

 

[pbaldy]

Well, the original code treated Project_Number as a numeric data type. The error message is telling you that there's a mismatch, which meant that the data type is text. My code adds the quotes around the value which are required for text values.

Your code to use a variable instead of the form reference directly would not have worked, as it still assumed a numeric data type (it would work fine with a numeric data type). I wouldn't normally bother with declaring/setting/using a variable if I'm only going to use it once like that, but that's personal preference.

 

[PNGBill]

I understand, Thanks. I jumped to the conclusion that a control named Project_Number held a number.