N
Neil_Pope
Guest
Hi.
I have 3 queries on a DB I am working on that function a facility for archiving Inspection Reports printed.
It runs fine, but I need to tidy up the whole thing. In a nutshell, one query appends a temp table with the required data, this then feeds the report. The second query then appends the archive table with these records. The third query then deletes the temp table. Simple and easy enough.
Now, my problem:
I am writing a module that has three functions in: one for each of the queries specified above. However, when I write the second query , 'Append_Archive', I am having problems with my SELECT clause as it is simply too long to fint onto oneline! How can I seperate this over two or more lines like normal code ?
An example of my code is detailed below:
Public Function Append_Temp_Archive()
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String
Dim SQL4 As String
Dim SQL5 As String
DoCmd.SetWarnings (False)
SQL1 = " INSERT INTO tbl_Loler_temp ( Contract, ID, Description, SWL, DateOfLastExam, Defects, DateOfNext, DetailsOfTest, Location, SerialNo, DateOfManu, Interval, Remedy, DateToFix, SafeToOperate, Tested, FirstExamination, Install, DateOfExamination, Danger, BecomeDanger, DangerByWhen, Assembly, Lost, Site, JobNo, ColorCode, [Report Number], CompanyName, BillingAddress, City, Address1, Address2, Address3, PostCode, Telephone, Fax, Qualification )"
SQL2 = " SELECT Contract.Contract, Item.ID, Item.Description, Item.SWL, Item.DateOfLastExam, Item.Defects, Item.DateOfNext, Item.DetailsOfTest, Item.Location, Item.SerialNo, Item.DateOfManu, Item.Interval, Item.Remedy, Item.DateToFix, Item.SafeToOperate, Item.Tested, Item.FirstExamination, Item.Install, Item.DateOfExamination, Item.Danger, Item.BecomeDanger, Item.DangerByWhen, Item.Assembly, Item.Lost, Contract.Site, Contract.JobNo, Contract.ColorCode, Contract.[Report Number], Customer.CompanyName, Customer.BillingAddress, Customer.City, Team.Address1, Team.Address2, Team.Address3, Team.PostCode, Team.Telephone, Team.Fax, Inspector.Qualification"
SQL3 = " FROM Team RIGHT JOIN (Inspector RIGHT JOIN (Customer INNER JOIN (Contract INNER JOIN Item ON Contract.Contract = Item.Contract) ON Customer.CustomerID = Contract.Customer) ON Inspector.ID = Item.Inspector) ON Team.Team = Inspector.Team"
SQL4 = " WHERE (((Contract.Contract)=[Forms]![PrintLoler]!
[Contract]) AND ((Item.Scrapped)=No))"
SQL5 = " ORDER BY Item.ID, Item.Location;"
DoCmd.RunSQL SQL1 + SQL2 + SQL3 + SQL4 + SQL5
End Function
This is the code for the first query and I have no probs with that.
Does anyone know how to help me on this?
-NEIL
I have 3 queries on a DB I am working on that function a facility for archiving Inspection Reports printed.
It runs fine, but I need to tidy up the whole thing. In a nutshell, one query appends a temp table with the required data, this then feeds the report. The second query then appends the archive table with these records. The third query then deletes the temp table. Simple and easy enough.
Now, my problem:
I am writing a module that has three functions in: one for each of the queries specified above. However, when I write the second query , 'Append_Archive', I am having problems with my SELECT clause as it is simply too long to fint onto oneline! How can I seperate this over two or more lines like normal code ?
An example of my code is detailed below:
Public Function Append_Temp_Archive()
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String
Dim SQL4 As String
Dim SQL5 As String
DoCmd.SetWarnings (False)
SQL1 = " INSERT INTO tbl_Loler_temp ( Contract, ID, Description, SWL, DateOfLastExam, Defects, DateOfNext, DetailsOfTest, Location, SerialNo, DateOfManu, Interval, Remedy, DateToFix, SafeToOperate, Tested, FirstExamination, Install, DateOfExamination, Danger, BecomeDanger, DangerByWhen, Assembly, Lost, Site, JobNo, ColorCode, [Report Number], CompanyName, BillingAddress, City, Address1, Address2, Address3, PostCode, Telephone, Fax, Qualification )"
SQL2 = " SELECT Contract.Contract, Item.ID, Item.Description, Item.SWL, Item.DateOfLastExam, Item.Defects, Item.DateOfNext, Item.DetailsOfTest, Item.Location, Item.SerialNo, Item.DateOfManu, Item.Interval, Item.Remedy, Item.DateToFix, Item.SafeToOperate, Item.Tested, Item.FirstExamination, Item.Install, Item.DateOfExamination, Item.Danger, Item.BecomeDanger, Item.DangerByWhen, Item.Assembly, Item.Lost, Contract.Site, Contract.JobNo, Contract.ColorCode, Contract.[Report Number], Customer.CompanyName, Customer.BillingAddress, Customer.City, Team.Address1, Team.Address2, Team.Address3, Team.PostCode, Team.Telephone, Team.Fax, Inspector.Qualification"
SQL3 = " FROM Team RIGHT JOIN (Inspector RIGHT JOIN (Customer INNER JOIN (Contract INNER JOIN Item ON Contract.Contract = Item.Contract) ON Customer.CustomerID = Contract.Customer) ON Inspector.ID = Item.Inspector) ON Team.Team = Inspector.Team"
SQL4 = " WHERE (((Contract.Contract)=[Forms]![PrintLoler]!
[Contract]) AND ((Item.Scrapped)=No))"
SQL5 = " ORDER BY Item.ID, Item.Location;"
DoCmd.RunSQL SQL1 + SQL2 + SQL3 + SQL4 + SQL5
End Function
This is the code for the first query and I have no probs with that.
Does anyone know how to help me on this?
-NEIL
