Syntax Error in RunSQL

[mdmarney]

Hi. Having trouble with the following command:

DoCmd.RunSQL "UPDATE tbl_attendants SET Arrival_Verification = '" &Yes& "' WHERE Me.Parish_Group_Arrival = '"&Yes&"'"

Receiving syntax error.
Help... *************
M. MARNEY

http://www.tgw.com

 

[r937]

these are Yes/No columns? try one of these...

SET Arrival_Verification = Yes
WHERE Me.Parish_Group_Arrival = True

or

SET Arrival_Verification = -1
WHERE Me.Parish_Group_Arrival <> 0


rudy

 

[mdmarney]

Tried it. Didn't work.
Is my Syntax bad?
DoCmd.RunSQL &quot;UPDATE tbl_attendants SET Arrival_Verification = '-1' WHERE Me.Parish_Group_Arrival = '<>0'&quot; *************
M. MARNEY

http://www.tgw.com

 

[swampBoogie]

Which dataypes are the columns? What is ME? Another table? If so, how is it related to tbl_attendants?

 

[r937]

yes, your syntax is bad, you added single quotes to what i gave you

do not use quotes around numeric values, or around the boolean keywords Yes or False

 

[mdmarney]

swamp - yes they are...

the following worked, but with a few complications:
DoCmd.RunSQL &quot;UPDATE tbl_attendants SET Arrival_Verification = TRUE WHERE Parish_Group_Arrival = TRUE&quot;

1) I wants to update the whole table. I need to add something to the where clause to indicate the link between the form and subform. (i.e. Where Parish_Group_ID = &quot; & Me![Parish_Group_ID]) How do I do this?

2) Is there anyway to remove the Update Table verification screen. The code errors out if you hit NO on that screen. I'd prefer to either give a custom dialog or no option at all. Ideas?

Thanks guys!
*************
M. MARNEY

http://www.tgw.com