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

Kamis, 18 Juli 2013

RE: [MS_AccessPros] DB.execute Update Syntax Help

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@roadsysinc.com
>
> 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