Error: mismatched data types in the criterion expression

[meme'1992]

In the debug of a vba form, i have this error "mismatched data types in the criterion expression"

Private Sub btn_find_Click()
    Dim tmpRS As DAO.Recordset
    Set tmpRS = CurrentDb.OpenRecordset(" SELECT name FROM Person WHERE [Surname]= '" & Me.txt_Surname & "' AND [Date] = '" & Me.txt_Date & "' ")
    Me.txt_name = tmpRS.Fields(0)
End Sub

I think is wrong the date. The criterios is #. Have I to convert?

 

[arnelgp]

Private Sub btn_find_Click()
    Dim tmpRS As DAO.Recordset
    Set tmpRS = CurrentDb.OpenRecordset(" SELECT name FROM Person WHERE [Surname]= '" & Me.txt_Surname & "' AND [Date] = #" & Format(Me.txt_Date,"mm/dd/yyyy") & "#")
    Me.txt_name = tmpRS.Fields(0)
End Sub

 

[Minty]

Two things - your date needs formatting correctly, have a read here - http://allenbrowne.com/ser-36.html

Secondly name is a reserved word - surround it in [name] or better still change it to something else.

 

[Gasman]

Yes, you use # for dates.

I tend to use a custom format nowadays

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

then something like

TempVars("StartDate") = Format(Me.txtDateBW, strcJetDate)

then

strSQL = strSQL & " WHERE (((tblEmployee.EndDate) Is Null) AND ((tblEmployee.Payroll)=True)) OR (((tblEmployee.EndDate) > " & [TempVars]![StartDate] & "))"

HTH

 

[meme'1992]

Thanks. It works!!