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;