Including SQL statement in VBA

[mr879]

Hello everyone I found a way to include SQL code into VBA without creating a query here is my code, but my problem is if I try to run it says there has to be an SQL statement in order to use the DoCmd.RunSql but clearly the SQL statement is within the variable strSQL here is the code.




Private Sub buttonNot_Click()

Dim strSQL As String
strSQL = "SELECT Table1.[FirstNam], Table1.[LastNam]" & _
"FROM Table1 " & _
"WHERE ((([FirstNam]) <> 'Jamie') AND (([LastNam]) <> 'Cartman'));"
DoCmd.RunSQL strSQL
Me.Filter = ""
Me.Filter = "FirstNam<>'Jamie' AND Lastnam<>'Cartman'"


End Sub

[pbaldy]

What it's telling you is that only action queries can be run with RunSQL; yours is not an action query.

[mr879]

I know how to create a regular Query but how do you make an action query? And after that should I reference the Query instead of the data table it self in the SQL statement? Such as SELECT Query1 instead of SELECT Table1

[pbaldy]

An action query is one that modifies data, not just displays it: append, update and delete queries. Perhaps you can explain what you're trying to accomplish?

[mr879]

I believe I can not use an action query then because my table which is linked through a SQL database which does not allow me to delete data and on top of that the information is updated daily so if I were to create a query would it display with the latest information? And I am trying to filter out the data from a table such as this one where I am trying to filter out any data wher the first and last nam are not equal to 'Jamie' and 'Cartman'

First Nam	Last Nam
Jamie	Lanestor
Green	Arrow
Eric	Cartman
Homer	Simpson
Jamie	ruhl
Pernelli	Cartman

[mr879]

Here is what I was thinking of doing just using regular code instead of queries or SQL but having trouble referencing the column without actually referencing the field name itself.

'Using a for loop to go through all the values within the column "First Nam" but Len takes the length of the text it self not the column so that is an issue
for i= 1 to Len("[First Nam]")

'If the current value in the field "First Nam" does not equal "Jamie" (The syntax is wrong I believe) then....
if ("[First Nam]"(i)<> "Jamie") Then

'I do not know how to filter the data here and append them at the same time I was thinking of something like this clearly the syntax is wrong
Me.Filter="[First Nam] = " & "First Nam"(i) & """"
end if

' Now doing the same thing for the second Column by having a nested for loop so that nothing is out of scope
for j=1 to Len("[Last Nam]")

if ("[Last Nam]"(j) <> "Cartman") Then

'Attempting to append the filtered values so that i can save my previous filtered data without trying to overwrite them.
Me.Filter= Me.Filter & " AND Last Nam = """ & "Last Nam"(i) & """"
end if

next
Me.FilterOn= True
next

[pbaldy]

I don't understand why you'd use a loop. A simple query will filter out those names, and you have the SQL for it in your first post.

[mr879]

I cant get them to work properly so I am trying different methods. But should I still make an action query even if I cannot modify the data from the table that I have right now?

[pbaldy]

I don't believe an action query is appropriate, but frankly I'm completely confused as to what you're trying to accomplish.

[mr879]

I know Queries can be used to easily Filter through the data in a table without the use of loops. So what I am trying to accomplish is a way to filter the data in the table except for the first and last names 'Jamie' and 'Cartman'. So after the data has been filtered the 'Jamie Lanestor', 'Eric Cartman', 'Jamie ruhl', and 'Pernelli Cartman' will no longer be displayed in the table.

First Nam	Last Nam
Jamie	Lanestor
Green	Arrow
Eric	Cartman
Homer	Simpson
Jamie	ruhl
Pernelli	Cartman

[mr879]

I have created a regular Query using the "Query Design" tab and I went in to SQL View and pasted this code in
SELECT [FirstNam], [LastNam]
FROM Table1
WHERE ((([FirstNam])<>'Jamie') AND (([LastNam])<>'Cartman'));

Then I ran the code and it asked me to input parameters for FirstNam and LastNam, why is that? I dont see anywhere in the code where it prompts the user I thought all this statement does is get all the data that is not Jamie and Cartman.

[mr879]

Any thoughts?

[June7]

Duplicate topic https://www.accessforums.net/program...ion-43859.html

[pbaldy]

I have created a regular Query using the "Query Design" tab and I went in to SQL View and pasted this code in
SELECT [FirstNam], [LastNam]
FROM Table1
WHERE ((([FirstNam])<>'Jamie') AND (([LastNam])<>'Cartman'));

Then I ran the code and it asked me to input parameters for FirstNam and LastNam, why is that? I dont see anywhere in the code where it prompts the user I thought all this statement does is get all the data that is not Jamie and Cartman.

Based on the picture, those fields have inadvisable spaces in them.

[mr879]

This is the solution thanks for the help everyone!!!


Me.Filter = ""
Me.Filter = "[First Nam]<>'Jamie' AND [Last Nam]<>'Cartman'"
Me.FilterOn = True