Pawel Pawel - 1 month ago 13
SQL Question

Access Run Time Error 3464 data type mismatch in criteria expresion

Please Help. What is wrong with that code? I can't figure out why I keep getting this error.

Sub renttt()
Dim rent_list As Recordset
Dim query As String

query = "SELECT * FROM (Rent INNER JOIN Movies ON Rent.Movie_ID = Movies.ID) INNER JOIN Customers ON Rent.Customer_ID = Customers.ID WHERE Rent.Movie_ID = '" & txtbxmovieID.Value & "' AND Rent.Date_Returned is Null;"
Set rent_list = CurrentDb.OpenRecordset(query)

If rent_list.RecordCount = 1 Then
rent_List.MoveFirst
txtbxname.Value = (rent_list![CusName])
txtbxsurname.Value = (rent_list![Surname])
txtbxcardID.Value = (rent_list![Id_Card_number])
txtbxaddress.Value = (rent_list![Address])
txtbxrented.Value = (rent_list![Date_Rent])
End If


End Sub

Answer

Wouldn't MovieId be a numeric? If so, no quotes:

WHERE Rent.Movie_ID = " & txtbxmovieID.Value & " AND ...