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

Kamis, 18 Juli 2013

RE: [MS_AccessPros] DB.execute Update Syntax Help

Doyce-

You have a table name that MUST be enclosed in brackets. Try this:

strSQL = "UPDATE [TEARDOWN-PRIMARY] SET Posted = True " & _
"WHERE Not (Posted);"

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dnwinberry
Sent: Thursday, July 18, 2013 5:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] DB.execute Update Syntax Help

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

------------------------------------

Yahoo! Groups Links