Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Statement BREAKING in my MODULE
- Thread starter bitech
- Start date
- Thread starter
- #3
tlbroadbent
MIS
How are you building the SQL statement? What does the VB code look like? Have you printed or displayed the SQL statemetn before executing it to make sure it is correct? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
- Thread starter
- #6
Yes to both of your questions. It works fine in the query, but it doesn't work when I paste it from the Query into the Module.
This is the code...
DoCmd.RunSQL"SELECT DISTINCTROW minority.*, MinSIC.*, Company.*, Roster.Main INTO Results
FROM Roster INNER JOIN ((((((((((((((((((minority INNER JOIN MinSIC ON minority.ID = MinSIC.ID) INNER JOIN Company ON MinSIC.ID = Company.ID) INNER JOIN [SearchAnnual Sales] ON Company.ID = [SearchAnnual Sales].ID) INNER JOIN [SearchCouncil Code] ON [SearchAnnual Sales].ID = [SearchCouncil Code].ID) INNER JOIN SearchDCertified ON [SearchCouncil Code].ID = SearchDCertified.ID) INNER JOIN SearchDDenied ON SearchDCertified.ID = SearchDDenied.ID) INNER JOIN SearchDRecertApp ON SearchDCertified.ID = SearchDRecertApp.ID) INNER JOIN [SearchEmployee Size] ON SearchDRecertApp.ID = [SearchEmployee Size].ID) INNER JOIN SearchEthnicity ON [SearchEmployee Size].ID = SearchEthnicity.ID) INNER JOIN SearchKeyword ON SearchEthnicity.ID = SearchKeyword.ID) INNER JOIN SearchZip ON SearchKeyword.ID = SearchZip.ID) INNER JOIN [SearchYear Established] ON SearchZip.ID = [SearchYear Established].ID) INNER JOIN SearchStatus ON [SearchYear Established].ID = SearchStatus.ID) INNER JOIN SearchSIC ON SearchStatus.ID = SearchSIC.ID) INNER JOIN SearchMarket ON SearchSIC.ID = SearchMarket.ID) INNER JOIN SearchDExpired ON SearchMarket.ID = SearchDExpired.ID) INNER JOIN SearchDPending ON SearchDExpired.ID = SearchDPending.ID) INNER JOIN SearchDRecert ON SearchDPending.ID = SearchDRecert.ID) ON Roster.[Company ID] = Company.ID
WHERE (((Roster.Main)=-1));"
What this is actual doing is connecting all of the Search Tables by Company ID numbers. This query should create a table called Results that will List all of the ID numbers that are listed in EVERY TABLE. {Which means, the company matched all criteria} The Minority Table, lists all of the information about the company, ex {Employee Size}, The Company Table lists all of the basic contact information about the company, ex {Address, Phone} and the Roster Table lists all of the contact people for the companies. I need all of these tables to connect and match up by ID and create a table called Results.
This is the code...
DoCmd.RunSQL"SELECT DISTINCTROW minority.*, MinSIC.*, Company.*, Roster.Main INTO Results
FROM Roster INNER JOIN ((((((((((((((((((minority INNER JOIN MinSIC ON minority.ID = MinSIC.ID) INNER JOIN Company ON MinSIC.ID = Company.ID) INNER JOIN [SearchAnnual Sales] ON Company.ID = [SearchAnnual Sales].ID) INNER JOIN [SearchCouncil Code] ON [SearchAnnual Sales].ID = [SearchCouncil Code].ID) INNER JOIN SearchDCertified ON [SearchCouncil Code].ID = SearchDCertified.ID) INNER JOIN SearchDDenied ON SearchDCertified.ID = SearchDDenied.ID) INNER JOIN SearchDRecertApp ON SearchDCertified.ID = SearchDRecertApp.ID) INNER JOIN [SearchEmployee Size] ON SearchDRecertApp.ID = [SearchEmployee Size].ID) INNER JOIN SearchEthnicity ON [SearchEmployee Size].ID = SearchEthnicity.ID) INNER JOIN SearchKeyword ON SearchEthnicity.ID = SearchKeyword.ID) INNER JOIN SearchZip ON SearchKeyword.ID = SearchZip.ID) INNER JOIN [SearchYear Established] ON SearchZip.ID = [SearchYear Established].ID) INNER JOIN SearchStatus ON [SearchYear Established].ID = SearchStatus.ID) INNER JOIN SearchSIC ON SearchStatus.ID = SearchSIC.ID) INNER JOIN SearchMarket ON SearchSIC.ID = SearchMarket.ID) INNER JOIN SearchDExpired ON SearchMarket.ID = SearchDExpired.ID) INNER JOIN SearchDPending ON SearchDExpired.ID = SearchDPending.ID) INNER JOIN SearchDRecert ON SearchDPending.ID = SearchDRecert.ID) ON Roster.[Company ID] = Company.ID
WHERE (((Roster.Main)=-1));"
What this is actual doing is connecting all of the Search Tables by Company ID numbers. This query should create a table called Results that will List all of the ID numbers that are listed in EVERY TABLE. {Which means, the company matched all criteria} The Minority Table, lists all of the information about the company, ex {Employee Size}, The Company Table lists all of the basic contact information about the company, ex {Address, Phone} and the Roster Table lists all of the contact people for the companies. I need all of these tables to connect and match up by ID and create a table called Results.
tlbroadbent
MIS
Here are two suggestions.
1) It doesn't appear that the query is dynamic. Create a query and save it. Then open the query from code. Use DoCmd.OpenQuery
2) Build a variable containing the the query and execute the use the variable in the Runsql method.
Dim strSQL As String
' Note: I use a combination of continuation
' lines and concatenation for example only
' You can use either or both methods
StrSQl = "SELECT DISTINCTROW minority.*, MinSIC.*," & _
"Company.*, Roster.Main INTO Results " & _
"FROM Roster INNER JOIN "
StrSQl = StrSQl & "((((((((((((((((((minority " & _
"INNER JOIN MinSIC ON minority.ID= "
<and so forth>
' print to immediate or debug window
' to verify that SQL statement is correct
Debug.Print strSQL
' run the SQL statement
DoCmd.RunSQL strSQL Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
- Status