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

Senin, 05 Mei 2014

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

Brian, Duane and John, thanks for helping me get this going.

Brian, I had actually tried your suggested construct originally but couldn't get it to work. What you provided works just fine, so I must have had a typo in my original attempt.

John, I appreciate the pointer on using Debug.Print to troubleshoot my strings to see what the SQL Parser is actually seeing. That will help me in the future.

Duane, you suggestion is what I have implemented because of it's simplicity, and I never would have thought to use NZ in that way. In fact, I modified you approach to simplify even a bit further based on information about my data that I didn't reveal in my original posting. Here's what I'm actually using:

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

"WHERE Nz(PartyVoted,'R') NOT IN ('R','D');"

So thanks again to all of you for moving me further down the road and helping me to learn!!

——

James

www.james-mc.com

Words To Live By

From: John Viescas johnv@msn.com
Reply: ms_access_professionals@yahoogroups.com ms_access_professionals@yahoogroups.com
Date: May 4, 2014 at 12:29:06 AM
To: ms_access_professionals@yahoogroups.com ms_access_professionals@yahoogroups.com
Subject: 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

www.james-mc.com

Words To Live By