Help to Exclude Records SQL

[rebelscum0000]

Dear All,

I make a new Table (SQL 7), and then I moved into this Table (SQL2) all
the Records
Like C:\Documents and Settings.
Is possible to set MyInclude = -1 ((Yes/No, True/False, or On/Off).
When the
The SQL2 is executing ? or I have to Update???

SQL MAKE A NEW TABLE

sQL7 = "SELECT MainExclude_Tbl.ID, MainExclude_Tbl.MyKeyword, " & _
"MainExclude_Tbl.MyAppz, MainExclude_Tbl.FileName, " & _
"MainExclude_Tbl.FolderPath, MainExclude_Tbl.FileExtension, " & _
"MainExclude_Tbl.FileType, MainExclude_Tbl.FileSize, " & _
"MainExclude_Tbl.FileModified, MainExclude_Tbl.FileAccessed, " & _
"MainExclude_Tbl.FileCreated, MainExclude_Tbl.FileAttributes, " & _
"MainExclude_Tbl.MyInclude INTO [" & AppzDocuSet & "] " & _
"FROM MainExclude_Tbl " & _
"WHERE False;"

CurrentDb.Execute sQL7, dbFailOnError

SQL INSERT INTO

SQL2 = _
"INSERT INTO [" & MyAppzDocuSet & "] ( ID, MyKeyword, " & _
"MyAppz, FileName, FolderPath, FileExtension, FileType, " & _
"FileSize, FileModified, FileAccessed, FileCreated, " & _
"FileAttributes, MyInclude ) " & _
"SELECT MainExclude_Tbl.ID, MainExclude_Tbl.MyKeyword, " & _
"MainExclude_Tbl.MyAppz, MainExclude_Tbl.FileName, " & _
"MainExclude_Tbl.FolderPath, MainExclude_Tbl.FileExtension, " & _
"MainExclude_Tbl.FileType, MainExclude_Tbl.FileSize, " & _
"MainExclude_Tbl.FileModified, MainExclude_Tbl.FileAccessed, " & _
"MainExclude_Tbl.FileCreated, MainExclude_Tbl.FileAttributes, " & _
"MainExclude_Tbl.MyInclude " & _
"FROM MainExclude_Tbl " & _
"WHERE FolderPath Like 'C:\Documents and Settings*'" & _
"ORDER BY ID;"


CurrentDb.Execute SQL2, dbFailOnError

Thank in advance
Kind Regards
Antonio Macias

 

[Douglas J. Steele]

You're missing a space between the WHERE clause and the ORDER BY clause:

"WHERE FolderPath Like 'C:\Documents and Settings*'" & _
"ORDER BY ID;"

needs to be

"WHERE FolderPath Like 'C:\Documents and Settings*' " & _
"ORDER BY ID;"

How do you determine the value of MyInclude? That will determine whether you
can set MyInclude in SQL7 or if you require a separate Update statement.

 

[Steve Schapel]

Antonio,

You mean like this?...

....
"MainExclude_Tbl.FileCreated, MainExclude_Tbl.FileAttributes, -1 AS
AllInclude" & _
"FROM MainExclude_Tbl " & _
....

By the way, I noticed there is a space missing after the WHERE clause.
Try...
"WHERE FolderPath Like 'C:\Documents and Settings*' " & _
"ORDER BY ID;"

 

[Steve Schapel]

Hehe, and then I left out the space in the example I gave you...

"MainExclude_Tbl.FileCreated, MainExclude_Tbl.FileAttributes, -1 AS
AllInclude " & _
"FROM MainExclude_Tbl " & _

 

[rebelscum0000]

HI again Gurus,

Well, the code Steve Schapel gave me works great, this is what I wanted
for now....
But I have in mind my next step I do determinate the value of MyInclude
based on all the records like C:\Documents and Settings were moved to a
Table 'anyname DocuSet_Tbl' from MainExclude_Tbl, I want to have
marked as '-1' in my Form
In order to remember that I do not to have to deal with these records,
so I will need a new SQL statement to update The Table MainExclude_Tbl
Where all the records from all the Tables Where all the records of
MyInclude Field are equal to -1

If this is confused, sorry I am tired, but I have to end this project
soon

What do you recommend to set before MyInclude = -1 in SQL7 or left the
code as Steve Schapel Gave me?

And later update. At this moment, I do not see another way


Thanks in advance
Kind Regards
Antonio Macias

 

[Steve Schapel]

Antonio,

I am sorry, I am not really clear what you are trying to do here. First
you use SQL7 which is a Make-Table Query to create a table with no
records. Then you use SQL2 which is an Append Query to insert data into
this table. Well, for one thing, I think this could be done in one
step, in other words a single Make-Table Query to create the table
including the required data. Anyway, your original question related to
wanting all records to have the value of -1 in the MyInclude field - is
that right? And now, are you saying you want the value of MyInclude to
be -1 for some of the records and not others? If so, can you say how we
can determine which records will have MyInclude = -1 and which will not?
Maybe you can provide an example?

 

[rebelscum0000]

Dear Steve Schapel,

I think this could be done in one
step, in other words a single Make-Table Query to create the table
including the required data

Could you please provide me with a example, How to Make-Table Query to
create the
Table including the required data in one step?

Lets say I want FileName set to "Readme" and FileExtions set to
".txt"

sQL7 = "SELECT MainExclude_Tbl.ID, MainExclude_Tbl.MyKeyword, " & _
"MainExclude_Tbl.MyAppz, MainExclude_Tbl.FileName, " & _
"MainExclude_Tbl.FolderPath, MainExclude_Tbl.FileExtension, " & _
"MainExclude_Tbl.FileType, MainExclude_Tbl.FileSize, " & _
"MainExclude_Tbl.FileModified, MainExclude_Tbl.FileAccessed, " & _
"MainExclude_Tbl.FileCreated, MainExclude_Tbl.FileAttributes, " & _
"MainExclude_Tbl.MyInclude INTO [" & AppzDocuSet & "] " & _
"FROM MainExclude_Tbl " & _
"WHERE False;"

Anyway, your original question related to

wanting all records to have the value of -1 in the MyInclude field - is

that right?

Yes, it was right but I had to re design my code and that took me a lot
of time also the New Code does not work at this moment with first code
you gave me

can you say how we

can determine which records will have MyInclude = -1 and which will
not?

Well, hard to explain but I have in mind an example
All the records that I have in a Table like "C:\Documents and
Settings" I moved to a another table and have to have MyInclude=-1
All the records that I have in a Table Like "E:\ " I Will move to
another table, and also MyInclude = -1
But as I said my code changed and I think with the example I am asking
you I can learn more

Thank you very much In advance
Kind Regards
Antonio Macias