SQL Statement BREAKING in my MODULE

[bitech]

I have a VB command that I'm trying to run to get my db to create a table. The problem is the code is apparently too long to fit on one line and it is wrapping, therefore breaking the code. Any help is welcomed.

 

[GGleason]

How many characters are in the SQL statement?

GGleason

 

[bitech]

1,416 characters

 

[GGleason]

Have you tried to paste your SQL statement from your VB code into an SQL window of a query? If so, did it run successfully?

 

[tlbroadbent]

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.

 

[bitech]

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.

 

[tlbroadbent]

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.