Update SQL error 3075 with Boolean field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ridders - 27 May 2008 21:36 GMT

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!!!

Reply to this Message

Dirk Goldgar - 27 May 2008 21:54 GMT

> Hi
>
[quoted text clipped - 22 lines]
>
> 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]"

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

ridders - 27 May 2008 21:55 GMT

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]));"

> Hi
>
[quoted text clipped - 21 lines]
>
> NOTE: This all works perfectly as an update query!!!

Reply to this Message