error 3129 invalid sql statement

[din2005]

Hi i have this sql code on frmTransferClinic form and under the command button transfer the code is this

strSQLTransfer = "UPDATE tblPatientClinics SET tblPatientClinics.ClinicDate = txtChooseClinicDate, tblPatientClinics.Consultant = cboConsultant, tblPatientClinics.TransferClinic = chkTransfer, tblPatientClinics.ReasonforCancellation = txtReasonTransfer " & _
"WHERE (((tblPatientClinics.ClinicDate)=[Me]![txtConsultantLst]) AND ((tblPatientClinics.Consultant)=[Me]![txtClinicDatelst]));"

DoCmd.RunSQL strSLQTransfer

but i get an error saying invalid sql statement expected delete, insert, procedure, select and update!

Could u tell me where i have gone wrong!

 

[Golom]

Try

strSQLTransfer = "UPDATE tblPatientClinics C " & _
"  SET C.ClinicDate = #" & txtChooseClinicDate & "#, " & _
"      C.Consultant = '" & cboConsultant & "', " & _
"      C.TransferClinic = '" & chkTransfer & "', " & _
"      C.ReasonforCancellation = '" & txtReasonTransfer &"' " & _
"WHERE C.ClinicDate=#" & [Me]![txtConsultantLst] & "' " & _
"      AND C.Consultant= '" & [Me]![txtClinicDatelst] & "';"

 

[din2005]

hi i tried your solution and now get an error msg saying

run time error 2465

"Microsoft can't find the field | to in your expression"

strSQLTransfer = "UPDATE tblPatientClinics C " & _
" SET C.ClinicDate = #" & txtChooseClinicDate & "#, " & _
" C.Consultant = '" & cboConsultant & "', " & _
" C.TransferClinic = '" & chkTransfer & "', " & _
" C.ReasonforCancellation = '" & txtReasonTransfer & "' " & _
"WHERE C.ClinicDate=#" & [Me]![txtClinicDatelst] & "# " & _
" AND C.Consultant= '" & [Me]![txtConsultantLst] & "';