Update SQL error 3075 with Boolean field

[ridders]

Hi

I am trying to use an update SQL statement to clear the values of several
text fields and change a Boolean field Attended to False
I get error 3075 when I run the SQL statement:

strSQL4 = "UPDATE tblParentAppointments SET tblParentAppointments.PupilID =
""," & _
" tblParentAppointments.ParentID = "",
tblParentAppointments.Student = ""," & _
" tblParentAppointments.ParentName = "",
tblParentAppointments.Phone = ""," & _
" tblParentAppointments.Attended = '0'" & _
" WHERE
(((tblParentAppointments.ApptID)=[Forms]![frmParentAppointments]![txtApptID]));"

DoCmd.RunSQL strSQL4

I believe the error is in the Boolean field.
I have tried replacing the '0' with False; 'False' and 0.
The error still occurs

Ideas on how to solve please

NOTE: This all works perfectly as an update query!!!

 

[Dirk Goldgar]

Hi

I am trying to use an update SQL statement to clear the values of several
text fields and change a Boolean field Attended to False
I get error 3075 when I run the SQL statement:

strSQL4 = "UPDATE tblParentAppointments SET tblParentAppointments.PupilID
=
""," & _
" tblParentAppointments.ParentID = "",
tblParentAppointments.Student = ""," & _
" tblParentAppointments.ParentName = "",
tblParentAppointments.Phone = ""," & _
" tblParentAppointments.Attended = '0'" & _
" WHERE
(((tblParentAppointments.ApptID)=[Forms]![frmParentAppointments]![txtApptID]));"

DoCmd.RunSQL strSQL4

I believe the error is in the Boolean field.
I have tried replacing the '0' with False; 'False' and 0.
The error still occurs

Ideas on how to solve please

NOTE: This all works perfectly as an update query!!!

Presumably you mean it works when you build the query in the visual query
designer, but the SQL you posted coulldn't work, and it really has nothing
to do with the boolean field. Did you try displaying the value of strSQL4
before you executed it, to see what it contains? This is what I get,
modified to put it onto multiple lines for clearer posting:

UPDATE tblParentAppointments SET
tblParentAppointments.PupilID = ",
tblParentAppointments.ParentID = ",
tblParentAppointments.Student = ",
tblParentAppointments.ParentName = ",
tblParentAppointments.Phone = ",
tblParentAppointments.Attended = '0'
WHERE (((tblParentAppointments.ApptID)=
[Forms]![frmParentAppointments]![txtApptID]));

Your problem mainly comes because of the double-quotes inside the quoted
literal. Try this code instead:

strSQL4 = _
"UPDATE tblParentAppointments SET" & _
" tblParentAppointments.PupilID = ''," & _
" tblParentAppointments.ParentID = ''," & _
" tblParentAppointments.Student = ''," & _
" tblParentAppointments.ParentName = ''," & _
" tblParentAppointments.Phone = ''," & _
" tblParentAppointments.Attended = False" & _
" WHERE tblParentAppointments.ApptID=" & _
"[Forms]![frmParentAppointments]![txtApptID]"

 

[ridders]

Oops - fixed it.
I should of course have been using single quotes throughout!
Corrected version:

strSQL4 = "UPDATE tblParentAppointments SET tblParentAppointments.PupilID =
''," & _
" tblParentAppointments.ParentID = '',
tblParentAppointments.Student = ''," & _
" tblParentAppointments.ParentName = '',
tblParentAppointments.Phone = ''," & _
" tblParentAppointments.Attended = 'False' " & _
" WHERE
(((tblParentAppointments.ApptID)=[Forms]![frmParentAppointments]![txtApptID]));"