Matching Date Formats for Query in a form

[WickidWe]

In the following Query, in the where clause the date selected in the form is to match a date in a table.

sSQL = "UPDATE ((tblRosterRec INNER JOIN tblEmpDetails ON tblRosterRec.EmpId = tblEmpDetails.EmpId) " & _
"INNER JOIN tblFunctions ON tblRosterRec.FctnID = tblFunctions.ReliefCode) " & _
"INNER JOIN tblRelief_Allot ON (tblFunctions.ReliefCode = tblRelief_Allot.ReliefCode) " & _
"AND (tblEmpDetails.EmpId = tblRelief_Allot.EmpId) " & _
"SET tblEmpDetails.IsVisible = False, tblRelief_Allot.FctnSelected = True " & _
"WHERE tblRosterRec.Date_=" & txtDate & " AND tblEmpDetails.EmpId=tblRosterRec.EmpId;"

this is not doing anything do I need to format the date first?

this is the query I made in the queryBuilder and it works a fine.



UPDATE ((tblRosterRec INNER JOIN tblEmpDetails ON tblRosterRec.EmpId = tblEmpDetails.EmpId) INNER JOIN tblFunctions ON tblRosterRec.FctnID = tblFunctions.ReliefCode) INNER JOIN tblRelief_Allot ON (tblFunctions.ReliefCode = tblRelief_Allot.ReliefCode) AND (tblEmpDetails.EmpId = tblRelief_Allot.EmpId) SET tblEmpDetails.IsVisible = False, tblRelief_Allot.FctnSelected = True
WHERE tblRosterRec.Date_=[Forms]![Form1]![txtDate] AND tblEmpDetails.EmpId=tblRosterRec.EmpId;

I set up a Debug.Print for the SQL and below is the result

UPDATE ((tblRosterRec INNER JOIN tblEmpDetails ON tblRosterRec.EmpId = tblEmpDetails.EmpId) INNER JOIN tblFunctions ON tblRosterRec.FctnID = tblFunctions.ReliefCode) INNER JOIN tblRelief_Allot ON (tblFunctions.ReliefCode = tblRelief_Allot.ReliefCode) AND (tblEmpDetails.EmpId = tblRelief_Allot.EmpId) SET tblEmpDetails.IsVisible = False, tblRelief_Allot.FctnSelected = True WHERE tblRosterRec.Date_=15/01/2014 AND tblEmpDetails.EmpId=tblRosterRec.EmpId;

[pbaldy]

You need to surround the value with #

"WHERE tblRosterRec.Date_=#" & txtDate & "# AND...