You need to add Option Explicit to all of your modules. This will require all variables to be declared.
The problem is you are trying to run a select query and DoCmd.RunSQL is to run an action query.
[cid:image003.jpg@01D25B8D.F813F6E0]
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, December 21, 2016 1:19 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Run Sql statement in vba question
John,
I have
Option Compare Database typed
Jim Wagner
________________________________
On Wednesday, December 21, 2016 11:06 AM, "John Viescas JohnV@msn.com<mailto:JohnV@msn.com> [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>> wrote:
Jim-
Did you do this?
SQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>""~"") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
(Paris, France)
On Dec 21, 2016, at 6:54 PM, Jim Wagner luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>> wrote:
I tried both quotes and I am getting an error with
single quotes and double double quotes of Invalid sql statement expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE
Jim Wagner
________________________________
On Wednesday, December 21, 2016 10:49 AM, "'Embrey, Darrell' DEmbrey@bcbsm.com<mailto:DEmbrey@bcbsm.com> [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>> wrote:
Jim
It isn't the tilde, it' the quotation marks inside the string. In order to use quotation marks inside the string, you must double them like so: ""~"" Or you could use the apostrophe '~'.
What is happening is the SQL engine thinks the quotation mark before the tilde is terminating the string.
Darrell
From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, December 21, 2016 12:38 PM
To: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Run Sql statment in vba question
Hello all,
I am trying to run a sql statement below and I am getting a syntax error on the "~" part of the statement. Is there are problem with "~" in vba?
Dim QSQL As String
SQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
DoCmd.RunSQL QSQL
Thank You for your help.
Jim Wagner
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
[Non-text portions of this message have been removed]
Posted by: "Embrey, Darrell" <DEmbrey@bcbsm.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
Tidak ada komentar:
Posting Komentar