SQL in VB

[apples76]

I am trying to run an SQL query in vb to return a recordset.

i m using the following code (or trying to)

Dim Recordset As New ADODB.Recordset
Dim SQL As String
SQL = ""


When i try and paste the following code inbetween the "" it insterts below the SQL = "" statement and highlights the whole text in red and it wont let me move it into the "". the code is copied from a working query which i currently use.

SELECT Planning_DWGs_tbl.Auto_Number_Planning_dwgs, Planning_DWGs_tbl.Planning_Drawings_Received, Planning_DWGs_tbl.Radio_Approve_Rejected, Planning_DWGs_tbl.Radio_Engineer, Planning_DWGs_tbl.Date_of_Review_Radio, Planning_DWGs_tbl.Radio_Comments, Planning_DWGs_tbl.Imp_Managers_Name, Planning_DWGs_tbl.Imp_Approved_Rejected, Planning_DWGs_tbl.Date_Of_Review_Build, Planning_DWGs_tbl.Build_Comments, Planning_DWGs_tbl.[Drawing(1)_Number], Planning_DWGs_tbl.[Revision(1)], Planning_DWGs_tbl.[Drawing(2)_Number], Planning_DWGs_tbl.[Revision(2)], Planning_DWGs_tbl.[Drawing(3)_Number], Planning_DWGs_tbl.[Revision(3)], Planning_DWGs_tbl.[Drawing(4)_Number], Planning_DWGs_tbl.[Revision(4)], Planning_DWGs_tbl.[Drawing(5)_Number], Planning_DWGs_tbl.[Revision(5)], Planning_DWGs_tbl.[Drawing(6)_Number], Planning_DWGs_tbl.[Revision(6)], Planning_DWGs_tbl.[Drawing(7)_Number], Planning_DWGs_tbl.[Revision(7)], Planning_DWGs_tbl.[Drawing(8)_Number], Planning_DWGs_tbl.[Revision(8)], Planning_DWGs_tbl.[Drawing(9)_Number], Planning_DWGs_tbl.[Revision(9)], Planning_DWGs_tbl.[Drawing(10)_Number], Planning_DWGs_tbl.[Revision(10)], CSR_tbl.CSR_Number, CSR_tbl.CSR_Name, CSR_Option_tbl.Option_Name, CSR_Option_tbl.Radio_Engineer_Name, contractors_tbl.Agents_Name, contractors_tbl.Lead_Consultant_Name, contractors_tbl.QS_Name
FROM ((CSR_tbl INNER JOIN CSR_Option_tbl ON CSR_tbl.Auto_Number_CSR_tbl = CSR_Option_tbl.Auto_Number_CSR_tbl) LEFT JOIN contractors_tbl ON CSR_Option_tbl.Auto_Number_option_tbl = contractors_tbl.Auto_Number_option_tbl) INNER JOIN Planning_DWGs_tbl ON CSR_Option_tbl.Auto_Number_option_tbl = Planning_DWGs_tbl.Auto_Number_option_tbl
WHERE (((Planning_DWGs_tbl.Auto_Number_Planning_dwgs)=[Receipt Ref]));

 

[KenHigg]

Is it bad juju to use something like 'SQL' as a variable name? Could that be the problem?

 

[workmad3]

I think the problem could be that it spans multiple lines. Either remove all the new lines, or go though and use the string conatenation and line continuation chars to make it see it over multiple lines (& and _ if you aren,t sure... remember to put a pace at the end of each line and wrap everything except the & and _ at the end of the line with ""

 

[allan57]

Try the following

"SELECT Planning_DWGs_tbl.Auto_Number_Planning_dwgs, Planning_DWGs_tbl.Planning_Drawings_Received, Planning_DWGs_tbl.Radio_Approve_Rejected, Planning_DWGs_tbl.Radio_Engineer, Planning_DWGs_tbl.Date_of_Review_Radio, Planning_DWGs_tbl.Radio_Comments, Planning_DWGs_tbl.Imp_Managers_Name, Planning_DWGs_tbl.Imp_Approved_Rejected, " & _
"Planning_DWGs_tbl.Date_Of_Review_Build , Planning_DWGs_tbl.Build_Comments, Planning_DWGs_tbl.[Drawing(1)_Number], Planning_DWGs_tbl.[Revision(1)], Planning_DWGs_tbl.[Drawing(2)_Number], Planning_DWGs_tbl.[Revision(2)], Planning_DWGs_tbl.[Drawing(3)_Number], Planning_DWGs_tbl.[Revision(3)], Planning_DWGs_tbl.[Drawing(4)_Number], " & _
"Planning_DWGs_tbl.[Revision(4)] , Planning_DWGs_tbl.[Drawing(5)_Number], Planning_DWGs_tbl.[Revision(5)], Planning_DWGs_tbl.[Drawing(6)_Number], Planning_DWGs_tbl.[Revision(6)], Planning_DWGs_tbl.[Drawing(7)_Number], Planning_DWGs_tbl.[Revision(7)], Planning_DWGs_tbl.[Drawing(8)_Number], Planning_DWGs_tbl.[Revision(8)], " & _
"Planning_DWGs_tbl.[Drawing(9)_Number], Planning_DWGs_tbl.[Revision(9)], Planning_DWGs_tbl.[Drawing(10)_Number], Planning_DWGs_tbl.[Revision(10)], CSR_tbl.CSR_Number, CSR_tbl.CSR_Name, CSR_Option_tbl.Option_Name, CSR_Option_tbl.Radio_Engineer_Name, contractors_tbl.Agents_Name, contractors_tbl.Lead_Consultant_Name, contractors_tbl.QS_Name " & _
"FROM ((CSR_tbl INNER JOIN CSR_Option_tbl ON CSR_tbl.Auto_Number_CSR_tbl = CSR_Option_tbl.Auto_Number_CSR_tbl) LEFT JOIN contractors_tbl ON CSR_Option_tbl.Auto_Number_option_tbl = contractors_tbl.Auto_Number_option_tbl) INNER JOIN Planning_DWGs_tbl ON CSR_Option_tbl.Auto_Number_option_tbl = Planning_DWGs_tbl.Auto_Number_option_tbl " & _
"WHERE (((Planning_DWGs_tbl.Auto_Number_Planning_dwgs)=[Receipt Ref]));"

 

[apples76]

Thanks

I think the problem could be that it spans multiple lines. Either remove all the new lines, or go though and use the string conatenation and line continuation chars to make it see it over multiple lines (& and _ if you aren,t sure... remember to put a pace at the end of each line and wrap everything except the & and _ at the end of the line with ""

cheers that has worked...

 

[apples76]

Thanks for the info so far.

but i am no having trouble with the exporting of the results to a word doc.

could somebody please help me as i am getting he code totally wrong...

 

[apples76]

Thanks for the info so far.

but i am no having trouble with the exporting of the results to a word doc.

could somebody please help me as i am getting he code totally wrong...