By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Breaking sql into multiple lines

P: 4
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!

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM [Current Orders] INNER JOIN (TblCurrency INNER JOIN TblBuyer ON TblCurrency.Currency = TblBuyer.Currency1) ON [Current Orders].Buyer = TblBuyer.Buyer
  3. WHERE ((([Current Orders].Inv_No)=[Please enter the invoice no]));
May 13 '10 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Current Orders].Cust_Order_No
  2.      , [Current Orders].New
  3.      , [Current Orders].Buyer
  4.      , [Current Orders].Brand
  5.      , [Current Orders].Wine
  6.      , [Current Orders].Vintage
  7.      , [Current Orders].Qty
  8.      , [Current Orders].Pack_Size
  9.      , [Current Orders].Price
  10.      , [Current Orders].Discount
  11.      , [price]*[QTY] AS [Gross Price]
  12.      , [Gross Price]*[discount] AS [Nett Price1]
  13.      , [GROSS PRICE]-[NETT PRICE1] AS [NETT PRICE2]
  14.      , TblBuyer.Terms
  15.      , TblBuyer.[Inco Term]
  16.      , TblBuyer.Freight_M
  17.      , TblBuyer.Currency1
  18.      , TblBuyer.Currency2
  19.      , TblBuyer.[Account No]
  20.      , TblBuyer.Country
  21.      , TblBuyer.Acc_Cust_Address1
  22.      , TblBuyer.AC_Tel1
  23.      , TblBuyer.AC_Tel2
  24.      , TblBuyer.AC_Tel3
  25.      , TblBuyer.AC_Fax1
  26.      , TblBuyer.AC_Email1
  27.      , TblBuyer.AC_Email2
  28.      , TblBuyer.AC_Email3
  29.      , TblBuyer.Del_Address1
  30.      , TblBuyer.Del_Address2
  31.      , TblBuyer.Del_Address3
  32.      , TblBuyer.Del_Address4
  33.      , TblBuyer.Cust_Notes
  34.      , TblCurrency.[Banking Details]
  35.      , TblCurrency.Notes
  36.      , [Current Orders].Est_DOS
  37.      , [Current Orders].Inv_No
  38.      , "APW0" & [inv_No] AS [Document No]
  39.      , TblBuyer.Terms_temp
  40.      , TblBuyer.Inv_Notes
  41.      , TblBuyer.Equipment
  42.      , TblBuyer.[Load Instruction]
  43.      , TblBuyer.Courier_Details
  44.      , TblBuyer.Contact_Cust
  45.      , TblBuyer.Tel_Cust
  46.      , TblBuyer.Contact_Courier
  47.      , TblBuyer.Tel_Courier
  48.      , TblBuyer.Contact_Consignee
  49.      , TblBuyer.Tel_Consignee
  50.      , TblBuyer.[Agent Involved]
  51.      , TblBuyer.POD
  52.      , TblBuyer.[Final Destination]
  53.      , TblBuyer.Insulation
  54.      , TblBuyer.[Documentation Requirements]
  55.      , [Current Orders].[Container No]
  56.      , [Current Orders].[Seal No]
  57.      , [Current Orders].Invoice_Date
  58.      , TblBuyer.VATNo
  59. FROM   [Current Orders] INNER JOIN
  60.        (TblCurrency INNER JOIN TblBuyer
  61.   ON   TblCurrency.Currency = TblBuyer.Currency1)
  62.   ON   [Current Orders].Buyer = TblBuyer.Buyer
  63. WHERE  ((([Current Orders].Inv_No)=[Please enter the invoice no]));
Welcome to Bytes!

Share this Question
Share on Google+
2 Replies


P: 4
Aaah, im getting to tired. Don't worry. Solved. Had '&_' instead of '& _'
May 13 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Current Orders].Cust_Order_No
  2.      , [Current Orders].New
  3.      , [Current Orders].Buyer
  4.      , [Current Orders].Brand
  5.      , [Current Orders].Wine
  6.      , [Current Orders].Vintage
  7.      , [Current Orders].Qty
  8.      , [Current Orders].Pack_Size
  9.      , [Current Orders].Price
  10.      , [Current Orders].Discount
  11.      , [price]*[QTY] AS [Gross Price]
  12.      , [Gross Price]*[discount] AS [Nett Price1]
  13.      , [GROSS PRICE]-[NETT PRICE1] AS [NETT PRICE2]
  14.      , TblBuyer.Terms
  15.      , TblBuyer.[Inco Term]
  16.      , TblBuyer.Freight_M
  17.      , TblBuyer.Currency1
  18.      , TblBuyer.Currency2
  19.      , TblBuyer.[Account No]
  20.      , TblBuyer.Country
  21.      , TblBuyer.Acc_Cust_Address1
  22.      , TblBuyer.AC_Tel1
  23.      , TblBuyer.AC_Tel2
  24.      , TblBuyer.AC_Tel3
  25.      , TblBuyer.AC_Fax1
  26.      , TblBuyer.AC_Email1
  27.      , TblBuyer.AC_Email2
  28.      , TblBuyer.AC_Email3
  29.      , TblBuyer.Del_Address1
  30.      , TblBuyer.Del_Address2
  31.      , TblBuyer.Del_Address3
  32.      , TblBuyer.Del_Address4
  33.      , TblBuyer.Cust_Notes
  34.      , TblCurrency.[Banking Details]
  35.      , TblCurrency.Notes
  36.      , [Current Orders].Est_DOS
  37.      , [Current Orders].Inv_No
  38.      , "APW0" & [inv_No] AS [Document No]
  39.      , TblBuyer.Terms_temp
  40.      , TblBuyer.Inv_Notes
  41.      , TblBuyer.Equipment
  42.      , TblBuyer.[Load Instruction]
  43.      , TblBuyer.Courier_Details
  44.      , TblBuyer.Contact_Cust
  45.      , TblBuyer.Tel_Cust
  46.      , TblBuyer.Contact_Courier
  47.      , TblBuyer.Tel_Courier
  48.      , TblBuyer.Contact_Consignee
  49.      , TblBuyer.Tel_Consignee
  50.      , TblBuyer.[Agent Involved]
  51.      , TblBuyer.POD
  52.      , TblBuyer.[Final Destination]
  53.      , TblBuyer.Insulation
  54.      , TblBuyer.[Documentation Requirements]
  55.      , [Current Orders].[Container No]
  56.      , [Current Orders].[Seal No]
  57.      , [Current Orders].Invoice_Date
  58.      , TblBuyer.VATNo
  59. FROM   [Current Orders] INNER JOIN
  60.        (TblCurrency INNER JOIN TblBuyer
  61.   ON   TblCurrency.Currency = TblBuyer.Currency1)
  62.   ON   [Current Orders].Buyer = TblBuyer.Buyer
  63. WHERE  ((([Current Orders].Inv_No)=[Please enter the invoice no]));
Welcome to Bytes!
May 14 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.