Invalid SQL Statement! 1

[din2005]

Hi all,

I get error message saying invalid sql statement! it works on the query section of the database in access when i place it code format i get the following error msg!

Any suggestions where i have gone wrong???

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

DoCmd.RunSQL strSLQTransfer

thanks

 

[mp9]

I think maybe it's because you need to replace your references to the Form objects with derived values, i.e. replace this:

strSQLTransfer = "UPDATE tblPatientClinics SET tblPatientClinics.ClinicDate = [Forms]![frmTransferClinic]![txtChooseClinicDate]....

With something like this:

strSQLTransfer = "UPDATE tblPatientClinics SET ClinicDate = #" & [Forms]![frmTransferClinic]![txtChooseClinicDate] & "#....

Note you need to enclose text values in quotes and date values in hashes (e.g. above). Numeric values can be left as they are.

 

[din2005]

i've tried that just now and i think i may have go something wrong, if anyone could see where it is it will be much obliged

strSQLTransfer = "UPDATE tblPatientClinics SET tblPatientClinics.ClinicDate = #" & [Forms]![frmTransferClinic]![txtChooseClinicDate] & "#, tblPatientClinics.Consultant = '" & [Forms]![frmTransferClinic]![cboConsultant] & "', tblPatientClinics.TransferClinic = [Forms]![frmTransferClinic]![chkTransfer], tblPatientClinics.[Other Comments] = '" & [Forms]![frmTransferClinic]![txtReasonTransfer] & "' " & _
"WHERE (((tblPatientClinics.ClinicDate)= #" & [Forms]![frmTransferClinic]![txtClinicDatelst] & "#) AND ((tblPatientClinics.Consultant)='" & [Forms]![frmTransferClinic]![txtConsultantLst] & "'));

 

[PHV]

You may try this:[tt]
strSQLTransfer = "UPDATE tblPatientClinics SET" & _
" ClinicDate=#" & Forms!frmTransferClinic!txtChooseClinicDate & "#" & _
",Consultant='" & Replace(Forms!frmTransferClinic!cboConsultant, "'", "''") & "'" & _
",TransferClinic=" & Forms!frmTransferClinic!chkTransfer " & _
",[Other Comments]='" & Replace(Forms!frmTransferClinic!txtReasonTransfer, "'", "''") & "'" & _
" WHERE ClinicDate=#" & Forms!frmTransferClinic!txtClinicDatelst & "#" & _
" AND Consultant='" & Replace(Forms!frmTransferClinic!txtConsultantLst, "'", "''") & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

thanks phv but code comes up as red! do u know what it could be?

 

[PHV]

Sorry for the typo on 4th line:[tt]
strSQLTransfer = "UPDATE tblPatientClinics SET" & _
" ClinicDate=#" & Forms!frmTransferClinic!txtChooseClinicDate & "#" & _
",Consultant='" & Replace(Forms!frmTransferClinic!cboConsultant, "'", "''") & "'" & _
",TransferClinic=" & Forms!frmTransferClinic!chkTransfer & _
",[Other Comments]='" & Replace(Forms!frmTransferClinic!txtReasonTransfer, "'", "''") & "'" & _
" WHERE ClinicDate=#" & Forms!frmTransferClinic!txtClinicDatelst & "#" & _
" AND Consultant='" & Replace(Forms!frmTransferClinic!txtConsultantLst, "'", "''") & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

thanks phv but i still get a run time error 3029 "invalid sql statement; expected 'delete', 'insert', 'procedure', 'select' or 'update'.

i don't know what could be the problem to it???

 

[PHV]

And which line is highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

it's ok got it to work! a silly error on my part! I think its been a long day! U Deserve a star!!!