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

Kamis, 18 Juli 2013

[MS_AccessPros] Re: DB.execute Update Syntax Help

John,

Thank you! That was it. Funny thing is I almost tried that but wasn't sure table names could go in brackets. I wish I had known 12 years ago when I named that table not to put a dash in there. But I'm learning more all the time and regret "sins" from the past.

Doyce

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
>