Solved SQL Statement

[Eljefegeneo]

What am I doing wrong? This is my sql statement from the query:

 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:

 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]

Hi. I could be wrong but maybe, I think, RunSQL is for actions queries only - not for select queries.

 

[Eljefegeneo]

So, what do I use for the run statement?

 

[theDBguy]

So, what do I use for the run statement?

If you're trying to display records, save it as a query and then use the OpenQuery method.

 

[Eljefegeneo]

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.

 

[Eljefegeneo]

And I should say that I use late binding.

 

[Isaac]

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]

My current 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]

 

[arnelgp]

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]

My current 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.

DoCmd.RunSQL method (Access)

Office VBA reference topic

docs.microsoft.com

Specifically, this portion.

 

[Eljefegeneo]

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]

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]

enjoy your evening and observe distancing.

 

[Eljefegeneo]

She is involved with local politics - on the city council as vice mayor - so I definitely keep my distance.