Solved SQL Statement (1 Viewer)

Eljefegeneo

Still trying to learn
Local time
Today, 00:40
Joined
Jan 10, 2011
Messages
870
What am I doing wrong? This is my sql statement from the query:
Code:
 SELECT TOP 25 tblMain.ClientID, tblMain.FirstName, tblMain.Organization, tblMain.Email, tblMain.OnSendList
FROM tblMain
WHERE ((Not (tblMain.Email) Is Null));
And this is my SQL statement in my vba code:
Code:
 Dim sSql As String
sSql = "SELECT TOP 25 tblMain.ClientID, tblMain.FirstName, tblMain.Organization, tblMain.Email, tblMain.OnSendList" & _
" FROM tblMain" & _
" WHERE ((Not (tblMain.Email) Is Null));"
DoCmd.RunSQL sSql
The former works of course. Running the latter says it is an inconsistent SQL statement.

Where am I going wrong?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:40
Joined
Oct 29, 2018
Messages
14,560
Hi. I could be wrong but maybe, I think, RunSQL is for actions queries only - not for select queries.
 

Eljefegeneo

Still trying to learn
Local time
Today, 00:40
Joined
Jan 10, 2011
Messages
870
So, what do I use for the run statement?
 

Eljefegeneo

Still trying to learn
Local time
Today, 00:40
Joined
Jan 10, 2011
Messages
870
Yes. I want to modify the query (Sql statement) with VBA using a parameter from a form. The use depends on whether or not I can run the SQL statement with VBA.
 

Isaac

Lifelong Learner
Local time
Today, 00:40
Joined
Mar 14, 2017
Messages
4,340
Can you share the screenshot that shows "inconsistent SQL statement" ?
I'm curious as that error doesn't sound familiar to me.

You should consider displaying the results of the query on a form or report, or exporting them, rather than directly opening what I assume is an updateable query. Opening it directly puts you in the same risk category as if you Opened a Table for a user - which then opens you up to people manually editing/damaging data in a completely uncontrolled interface environment.

See this for a method that I find very convenient in most 'parameter' needs cases.
 

Eljefegeneo

Still trying to learn
Local time
Today, 00:40
Joined
Jan 10, 2011
Messages
870
My current code:
Code:
 Dim sSQL As String
sSQL = "SELECT TOP 25 tblMain.ClientID, tblMain.FirstName, tblMain.Organization, tblMain.Email, tblMain.OnSendList" & _
" FROM tblMain" & _
" WHERE ((Not (tblMain.Email) Is Null));"
DoCmd.RunSQL sSQL[Code]
 

Attachments

  • ErrorStatement.jpg
    ErrorStatement.jpg
    33.6 KB · Views: 31

arnelgp

error reading drive A:
Local time
Today, 15:40
Joined
May 7, 2009
Messages
12,021
what is the parameter you need to add?
you can add it to your saved Query, eg:

Dim sSQL As String
Dim qd As DAO.Querydef
Dim db As DAO.Database

sSQL = "SELECT TOP " & Me!txtNumber & " tblMain.ClientID, tblMain.FirstName, tblMain.Organization, tblMain.Email, tblMain.OnSendList" & _
" FROM tblMain" & _
" WHERE ((Not (tblMain.Email) Is Null));"

Set db = Currentdb
Set qd = db.Querydefs("yourQueryName")
qd.SQL = sSQL
Set qd=Nothing
Set db=Nothing

DoCmd.OpenQuery "yourQueryName"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:40
Joined
Oct 29, 2018
Messages
14,560
My current code:
Code:
 Dim sSQL As String
sSQL = "SELECT TOP 25 tblMain.ClientID, tblMain.FirstName, tblMain.Organization, tblMain.Email, tblMain.OnSendList" & _
" FROM tblMain" & _
" WHERE ((Not (tblMain.Email) Is Null));"
DoCmd.RunSQL sSQL[Code]
Hi. Actually, I agree with @Isaac that you should consider using a Form to display the result of the SQL statement. Check out this article on RunSQL.

Specifically, this portion.
1600994438722.png
 

Eljefegeneo

Still trying to learn
Local time
Today, 00:40
Joined
Jan 10, 2011
Messages
870
what is the parameter you need to add?
you can add it to your saved Query, eg:

Dim sSQL As String
Dim qd As DAO.Querydef
Dim db As DAO.Database

sSQL = "SELECT TOP " & Me!txtNumber & " tblMain.ClientID, tblMain.FirstName, tblMain.Organization, tblMain.Email, tblMain.OnSendList" & _
" FROM tblMain" & _
" WHERE ((Not (tblMain.Email) Is Null));"

Set db = Currentdb
Set qd = db.Querydefs("yourQueryName")
qd.SQL = sSQL
Set qd=Nothing
Set db=Nothing

DoCmd.OpenQuery "yourQueryName"
 

Eljefegeneo

Still trying to learn
Local time
Today, 00:40
Joined
Jan 10, 2011
Messages
870
Thank you arnelgp. I see what you are saying now. You can only modify an existing select query this way. by the way, you have solved two problems for me today. Guess I can now go and make dinner for my wife.
 

arnelgp

error reading drive A:
Local time
Today, 15:40
Joined
May 7, 2009
Messages
12,021
enjoy your evening and observe distancing.
 

Eljefegeneo

Still trying to learn
Local time
Today, 00:40
Joined
Jan 10, 2011
Messages
870
She is involved with local politics - on the city council as vice mayor - so I definitely keep my distance.
 

Users who are viewing this thread

Top Bottom