ACCESS FEED: Re: [MS_AccessPros] Need help setting up query in VBA

Sabtu, 03 Mei 2014

Re: [MS_AccessPros] Need help setting up query in VBA

Excellent answer, Duane!

Additional tip: When you find SQL in code not working as expected, add:

Debug.Print SQL_text

.. to your code. Run your code and then go to the Immediate Window (CTRL+G). You should see the SQL you tried to execute laid out as VBA interpreted it. If the error is not obvious (you were missing a space at the end of each line), you can copy and paste the text into a new query and then switch to Design view to get the SQL Parser to give you a better clue as to why it doesn't work.

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)

On May 4, 2014, at 4:14 AM, Duane Hookom <duanehookom@hotmail.com> wrote:

I would simplify it to:
SQL_text = "UPDATE Votes SET PartyVoted = 'O' " & _
"WHERE Nz(PartyVoted,'G') IN ('G','NP','S','L');"

Duane Hookom MVP
MS Access

To: ms_access_professionals@yahoogroups.com
From: kc5qeg@gmail.com
Date: Sat, 3 May 2014 16:23:06 -0500
Subject: [MS_AccessPros] Need help setting up query in VBA

I'm trying to set up an update query to run in VBA but am having problems getting the syntax right. Here's the SQL from the QBE grid:

UPDATE Votes SET Votes.PartyVoted = "O" WHERE (((Votes.PartyVoted)="G" Or (Votes.PartyVoted)="NP" Or (Votes.PartyVoted)="S" Or (Votes.PartyVoted) Is Null Or (Votes.PartyVoted)="L"));

This query runs find when I manually execute it, but I want to be able to execute is as a part of a data import routine so I don't forget to run it. Here's what I have now, but it doesn't work presumedly because I don't have the O, G, NP, etc., properly "escaped" for VBA. But I'm sure there could be other issues as well.

-----

SQL_text = "UPDATE Votes SET Votes.PartyVoted = ""O""" & _

"WHERE (((Votes.PartyVoted)=""G""" & _

"Or (Votes.PartyVoted)=""NP""" & _

"Or (Votes.PartyVoted)=""S""" & _

"Or (Votes.PartyVoted) Is Null Or (Votes.PartyVoted)=""L""" & "));"

DoCmd.RunSQL SQL_text

——

How do I need to change this to the the query to execute properly? Using Access 2010.

Thanks for the help!

——

James