Breaking sql into multiple lines

[htdIO]

Hey guys. I'm doing my neighbor a favor and helping with her home business.

I'm new to sql and im trying to use a sql statement, but it is to long for one line. I dont know how to break it up into multiple lines without getting a compile error.

PS. The problem is the select line, it on it's own is too long. Some help would be great!

SELECT [Current Orders].Cust_Order_No, [Current Orders].New, [Current Orders].Buyer, [Current Orders].Brand, [Current Orders].Wine, [Current Orders].Vintage, [Current Orders].Qty, [Current Orders].Pack_Size, [Current Orders].Price, [Current Orders].Discount, [price]*[QTY] AS [Gross Price], [Gross Price]*[discount] AS [Nett Price1], [GROSS PRICE]-[NETT PRICE1] AS [NETT PRICE2], TblBuyer.Terms, TblBuyer.[Inco Term], TblBuyer.Freight_M, TblBuyer.Currency1, TblBuyer.Currency2, TblBuyer.[Account No], TblBuyer.Country, TblBuyer.Acc_Cust_Address1, TblBuyer.AC_Tel1, TblBuyer.AC_Tel2, TblBuyer.AC_Tel3, TblBuyer.AC_Fax1, TblBuyer.AC_Email1, TblBuyer.AC_Email2, TblBuyer.AC_Email3, TblBuyer.Del_Address1, TblBuyer.Del_Address2, TblBuyer.Del_Address3, TblBuyer.Del_Address4, TblBuyer.Cust_Notes, TblCurrency.[Banking Details], TblCurrency.Notes, [Current Orders].Est_DOS, [Current Orders].Inv_No, "APW0" & [inv_No] AS [Document No], TblBuyer.Terms_temp, TblBuyer.Inv_Notes, TblBuyer.Equipment, TblBuyer.[Load Instruction], TblBuyer.Courier_Details, TblBuyer.Contact_Cust, TblBuyer.Tel_Cust, TblBuyer.Contact_Courier, TblBuyer.Tel_Courier, TblBuyer.Contact_Consignee, TblBuyer.Tel_Consignee, TblBuyer.[Agent Involved], TblBuyer.POD, TblBuyer.[Final Destination], TblBuyer.Insulation, TblBuyer.[Documentation Requirements], [Current Orders].[Container No], [Current Orders].[Seal No], [Current Orders].Invoice_Date, TblBuyer.VATNo
FROM [Current Orders] INNER JOIN (TblCurrency INNER JOIN TblBuyer ON TblCurrency.Currency = TblBuyer.Currency1) ON [Current Orders].Buyer = TblBuyer.Buyer
WHERE ((([Current Orders].Inv_No)=[Please enter the invoice no]));

[htdIO]

Aaah, im getting to tired. Don't worry. Solved. Had '&_' instead of '& _'

[NeoPa]

I'm not sure whether you are talking about SQL here or VBA. SQL needs no special handling of broken lines as it recognises only general white-space. VBA on the other hand requires the underscore (_) character to indicate continuation between lines.

As an illustration I'll post your SQL reformatted so that it is more readable. There is no change to the effect of the SQL :

SELECT [Current Orders].Cust_Order_No
     , [Current Orders].New
     , [Current Orders].Buyer
     , [Current Orders].Brand
     , [Current Orders].Wine
     , [Current Orders].Vintage
     , [Current Orders].Qty
     , [Current Orders].Pack_Size
     , [Current Orders].Price
     , [Current Orders].Discount
     , [price]*[QTY] AS [Gross Price]
     , [Gross Price]*[discount] AS [Nett Price1]
     , [GROSS PRICE]-[NETT PRICE1] AS [NETT PRICE2]
     , TblBuyer.Terms
     , TblBuyer.[Inco Term]
     , TblBuyer.Freight_M
     , TblBuyer.Currency1
     , TblBuyer.Currency2
     , TblBuyer.[Account No]
     , TblBuyer.Country
     , TblBuyer.Acc_Cust_Address1
     , TblBuyer.AC_Tel1
     , TblBuyer.AC_Tel2
     , TblBuyer.AC_Tel3
     , TblBuyer.AC_Fax1
     , TblBuyer.AC_Email1
     , TblBuyer.AC_Email2
     , TblBuyer.AC_Email3
     , TblBuyer.Del_Address1
     , TblBuyer.Del_Address2
     , TblBuyer.Del_Address3
     , TblBuyer.Del_Address4
     , TblBuyer.Cust_Notes
     , TblCurrency.[Banking Details]
     , TblCurrency.Notes
     , [Current Orders].Est_DOS
     , [Current Orders].Inv_No
     , "APW0" & [inv_No] AS [Document No]
     , TblBuyer.Terms_temp
     , TblBuyer.Inv_Notes
     , TblBuyer.Equipment
     , TblBuyer.[Load Instruction]
     , TblBuyer.Courier_Details
     , TblBuyer.Contact_Cust
     , TblBuyer.Tel_Cust
     , TblBuyer.Contact_Courier
     , TblBuyer.Tel_Courier
     , TblBuyer.Contact_Consignee
     , TblBuyer.Tel_Consignee
     , TblBuyer.[Agent Involved]
     , TblBuyer.POD
     , TblBuyer.[Final Destination]
     , TblBuyer.Insulation
     , TblBuyer.[Documentation Requirements]
     , [Current Orders].[Container No]
     , [Current Orders].[Seal No]
     , [Current Orders].Invoice_Date
     , TblBuyer.VATNo
FROM   [Current Orders] INNER JOIN
       (TblCurrency INNER JOIN TblBuyer
  ON   TblCurrency.Currency = TblBuyer.Currency1)
  ON   [Current Orders].Buyer = TblBuyer.Buyer
WHERE  ((([Current Orders].Inv_No)=[Please enter the invoice no]));

Welcome to Bytes!