ACCESS FEED: Re: [MS_AccessPros] DB.execute Update Syntax Help

Kamis, 18 Juli 2013

Re: [MS_AccessPros] DB.execute Update Syntax Help

Thanks Duane. I usually do that but I'm trying to sharpen my skills writing code. And that is a good explanation. I wish I had known more about naming things 12 years ago when I created this table. Thanks again for your help and good advice.

Doyce

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> Doyce,
>
> I would recommend creating an update query and then viewing the SQL so you can copy and paste it into your code.
>
> Your table name is basically the expression TearDown minus Primary. If you need to use symbols and/or spaces in table/field names, you will need to use []s.
>
> I typically use 0 and -1 in place of false and true.
>
> Duane Hookom MVP
> MS Access
>
>
> ----------------------------------------
> > From: winberry.doyce@...
> >
> > Friends.
> >
> > I can't seem to get the syntax right to update a yes/no field in a table. I want to update all fields that are false to true. Here is my code:
> > 'Flag units sent up to True
> > Dim db As Database
> > Set db = CurrentDb
> > Dim strSQL As String
> > Dim strSQL1 As String
> >
> > strSQL = "UPDATE TEARDOWN-PRIMARY SET TEARDOWN-PRIMARY.[Posted] = True " & _
> > "WHERE ((TEARDOWN-PRIMARY.[Posted] = " & False & "));"
> > Debug.Print strSQL
> > strSQL1 = "UPDATE CitySemiTEARDOWN-PRIMARY SET CitySemiTEARDOWN-PRIMARY.[Posted] = True " & _
> > "WHERE ((CitySemiTEARDOWN-PRIMARY.[Posted] = " & False & "));"
> > Debug.Print strSQL1
> > db.Execute strSQL, dbFailOnError
> > db.Execute strSQL1, dbFailOnError
> >
> > MsgBox CStr(db.RecordsAffected) & " records updated.", vbInformation, "Records Updated"
> > Set db = Nothing
> >
> > It errors on the first strSQL statement.
> >
> > Doyce
>