How do I break a long single line into multiple line

[Ineedhelpplz]

How do I break the strSQL line into shorter lines? I know it's been asked here a thousand times but nothing seems to work. I keep getting "compile error: Expected: End of statement" Any recommendations for this novice would be great.

Sub Button10_Click()
Dim strSQL As String
 
Set appAccess = CreateObject("Access.Application")
 
appAccess.Visible = False
 
 
strSQL = "INSERT INTO [Finished Batches] ([ReleaseDate],[BatchSheetNo],[LotNumber],[BatchSize],[UOM],[BlenderNumber],[Ingr1],[Amt1],[Ing2],[Ing3],[Ing4],[Ing5],[Ing6],[Ing7],[Ing8],[Ing9],[Ing10],[Ing11],[Ing12],[Ing13],[Ing14],[Ing15],[Ing16],[Ing17],[Ing18],[Ing19],[Ing20],[Ing21],[Ing22],[Ing23],[Ing24],[Ing25],[Ing26],[Ing27],[Amt2],[Amt3],[Amt4],[Amt5],[Amt6],[Amt7],[Amt8],[Amt9],[Amt10],[Amt11],[Amt12],[Amt13],[Amt14],[Amt15],[Amt16],[Amt17],[Amt18],[Amt19],[Amt20],[Amt21],[Amt22],[Amt23],[Amt24],[Amt25],[Amt26],[Amt27],[Lot1a],[Lot2a],[Lot3a],[Lot4a],[Lot5a],[Lot6a],[Lot7a],[Lot8a],[Lot9a],[Lot10a],[Lot11a],[Lot12a],[Lot13a],[Lot14a],[Lot15a],[Lot16a],[Lot17a],[Lot18a],[Lot19a],[Lot20a],[Lot21a],[Lot22a],[Lot23a],[Lot24a],[Lot25a],[Lot26a],[Lot27a],[Amt1a],[Amt2a])" & _
         "VALUES (#" & Range("G2") & "#,'" & Range("D2") & "','" & Range("B2") & "'," & Range("k1") & ",'" & Range("L1") & "','" & Range("j2") & "','" & Range("c5") & _
         "'," & Range("J5") & ",'" & Range("C6") & "','" & Range("c7") & "','" & Range("c8") & "','" & Range("c9") & "','" & Range("c10") & "','" & Range("c11") & "','" & Range("c12") & _
         "','" & Range("c13") & "','" & Range("C14") & "','" & Range("c15") & "','" & Range("c16") & "','" & Range("c17") & "','" & Range("c18") & "','" & Range("c19") & "','" & Range("c20") & "','" & Range("c21") & _
         "','" & Range("C22") & "','" & Range("c23") & "','" & Range("c24") & "','" & Range("c25") & "','" & Range("c26") & "','" & Range("c27") & "','" & Range("c28") & "','" & Range("c29") & "','" & Range("c30") & _
         "','" & Range("c31") & "'," & Range("j6") & "," & Range("j7") & "," & Range("j8") & "," & Range("j9") & "," & Range("j10") & "," & Range("j11") & "," & Range("j12") & "," & Range("j13") & "," & Range("j14") & _
         "," & Range("j15") & "," & Range("j16") & "," & Range("j17") & "," & Range("j18") & "," & Range("j19") & "," & Range("j20") & "," & Range("j21") & "," & Range("j22") & "," & Range("j23") & "," & Range("j24") & _
         "," & Range("j25") & "," & Range("j26") & "," & Range("j27") & "," & Range("j28") & "," & Range("j29") & "," & Range("j30") & "," & Range("j31") & ",'" & Range("k5") & "','" & Range("k6") & "','" & Range("k7") & _
         "','" & Range("k8") & "','" & Range("k9") & "','" & Range("k10") & "','" & Range("k11") & "','" & Range("k12") & "','" & Range("k13") & "','" & Range("k14") & "','" & Range("k15") & "','" & Range("k16") & "','" & Range("k17") & "','" & Range("k18") & "','" & Range("k19") & "','" & Range("k20") & _
         "','" & Range("k21") & "','" & Range("k22") & "','" & Range("k23") & "','" & Range("k24") & "','" & Range("k25") & "','" & Range("k26") & "','" & Range("k27") & "','" & Range("k28") & "','" & Range("k29") & "','" & Range("k30") & "','" & Range("k31") & "'," & Range("L5") & "," & Range("L6") & ")"
 
 
With appAccess
  .OpenCurrentDatabase ("C:\users\jay neuhoff\desktop\database\ss database.mdb")
  Debug.Print strSQL
  .DoCmd.RunSQL strSQL, dbFailOnError
  .CloseCurrentDatabase
 
End With
 
Set appAccess = Nothing
 MsgBox ("saved")
End Sub
 

[Luuk]

Try this, it will be much more readable

 
 secondpart = ""
 strSQL = "INSERT INTO [Finished Batches] ( "
 srqSQL = strSQL & "[ReleaseDate], " 
 secondpart = secondpart & " #"&Range("G2")
 
 strsql = strsql & "BatchSheetNo], "
 secondpart = secondpart & ", '" & Range("D2") & "'"
 
 ....etc,etc...
 
 strsql = strsql & "(" & secondpart & ")";
 
 
 

[Ineedhelpplz]

Thanks, that is so much better. But I spent a lot of time trying to break down line 9 so I am still wondering if it is even possible, and if its not, why?

[Luuk]

It should be possible, like this

strSQL = "INSERT " & _ 
  "INTO " & _
      "[Finished Batches] (" & _
          "[ReleaseDate]," & _
          "[BatchSheetNo]," & _ 
etc,etc...
          "[Amt1a]," & _
          "[Amt2a])"
 

[Luuk]

But note the explanation here:
https://msdn.microsoft.com/en-us/lib.../gg278750.aspx
•A line contains too many characters. You can create a longer logical line by joining physical lines with a line-continuation character, a space followed by an underscore ( _). Up to 25 physical lines can be joined with line-continuation characters to form a single logical line, or 24 consecutive line-continuation characters. Thus, a logical line could potentially contain a total of 10,230 characters.

[Ineedhelpplz]

My problem is that the underscore causes a compile error. Give it a try. :)

[twinnyfo]

Ineedhlpplz,

Luuk's explanation in Post #5 is correct.

However, there is nothing that precludes you from breaking the creation of the creation of your string into multiple segments. E.G.:

strSQL = "INSERT INTO [Finished Batches] " & _ etc.
 
strSQL = strSQL & " VALUES (#" & Range("G2") & "#," & _ etc.

You can link as many of these partial segments together that you want.

This method may also help you trouble shoot if there are problems with your query.

[NeoPa]

INeedHlpPlz:
My problem is that the underscore causes a compile error. Give it a try. :)

I hate to contradict, but that's simply not true. It may be true that your code fails to compile after you've added undescores, but if so then you are not doing it correctly.

What is the compile error you're getting? That would be a good place to start. Remember that the explanation is quite explicit about ensuring there is a space before the underscore. If you don't include the space then it won't be recognised as a line-continuation. Also that only 24 can be used in a single logical line.

[Ineedhelpplz]

I did provide the compile error in my original post because I thought it would be a good place to start. Try using a space underscore in line 9. It should work. But doesn't. I've tried it 100 ways. see below

Sub Button10_Click()
Dim strSQL As String
 
Set appAccess = CreateObject("Access.Application")
 
appAccess.Visible = False
 
 
strSQL = "INSERT INTO [Finished Batches] ([ReleaseDate],[BatchSheetNo],[LotNumber],[BatchSize],[UOM],[BlenderNumber],[Ingr1], _
        [Amt1],[Ing2],[Ing3],[Ing4],[Ing5],[Ing6],[Ing7],[Ing8],[Ing9],[Ing10],[Ing11],[Ing12],[Ing13],[Ing14],[Ing15],[Ing16],[Ing17],[Ing18],[Ing19],[Ing20],[Ing21],[Ing22],[Ing23],[Ing24],[Ing25],[Ing26],[Ing27],[Amt2],[Amt3],[Amt4],[Amt5],[Amt6],[Amt7],[Amt8],[Amt9],[Amt10],[Amt11],[Amt12],[Amt13],[Amt14],[Amt15],[Amt16],[Amt17],[Amt18],[Amt19],[Amt20],[Amt21],[Amt22],[Amt23],[Amt24],[Amt25],[Amt26],[Amt27],[Lot1a],[Lot2a],[Lot3a],[Lot4a],[Lot5a],[Lot6a],[Lot7a],[Lot8a],[Lot9a],[Lot10a],[Lot11a],[Lot12a],[Lot13a],[Lot14a],[Lot15a],[Lot16a],[Lot17a],[Lot18a],[Lot19a],[Lot20a],[Lot21a],[Lot22a],[Lot23a],[Lot24a],[Lot25a],[Lot26a],[Lot27a],[Amt1a],[Amt2a])" & _
         "VALUES (#" & Range("G2") & "#,'" & Range("D2") & "','" & Range("B2") & "'," & Range("k1") & ",'" & Range("L1") & "','" & Range("j2") & "','" & Range("c5") & _
         "'," & Range("J5") & ",'" & Range("C6") & "','" & Range("c7") & "','" & Range("c8") & "','" & Range("c9") & "','" & Range("c10") & "','" & Range("c11") & "','" & Range("c12") & _
         "','" & Range("c13") & "','" & Range("C14") & "','" & Range("c15") & "','" & Range("c16") & "','" & Range("c17") & "','" & Range("c18") & "','" & Range("c19") & "','" & Range("c20") & "','" & Range("c21") & _
         "','" & Range("C22") & "','" & Range("c23") & "','" & Range("c24") & "','" & Range("c25") & "','" & Range("c26") & "','" & Range("c27") & "','" & Range("c28") & "','" & Range("c29") & "','" & Range("c30") & _
         "','" & Range("c31") & "'," & Range("j6") & "," & Range("j7") & "," & Range("j8") & "," & Range("j9") & "," & Range("j10") & "," & Range("j11") & "," & Range("j12") & "," & Range("j13") & "," & Range("j14") & _
         "," & Range("j15") & "," & Range("j16") & "," & Range("j17") & "," & Range("j18") & "," & Range("j19") & "," & Range("j20") & "," & Range("j21") & "," & Range("j22") & "," & Range("j23") & "," & Range("j24") & _
         "," & Range("j25") & "," & Range("j26") & "," & Range("j27") & "," & Range("j28") & "," & Range("j29") & "," & Range("j30") & "," & Range("j31") & ",'" & Range("k5") & "','" & Range("k6") & "','" & Range("k7") & _
         "','" & Range("k8") & "','" & Range("k9") & "','" & Range("k10") & "','" & Range("k11") & "','" & Range("k12") & "','" & Range("k13") & "','" & Range("k14") & "','" & Range("k15") & "','" & Range("k16") & "','" & Range("k17") & "','" & Range("k18") & "','" & Range("k19") & "','" & Range("k20") & _
         "','" & Range("k21") & "','" & Range("k22") & "','" & Range("k23") & "','" & Range("k24") & "','" & Range("k25") & "','" & Range("k26") & "','" & Range("k27") & "','" & Range("k28") & "','" & Range("k29") & "','" & Range("k30") & "','" & Range("k31") & "'," & Range("L5") & "," & Range("L6") & ")"
 
 
With appAccess
  .OpenCurrentDatabase ("C:\users\jay neuhoff\desktop\database\ss database.mdb")
  Debug.Print strSQL
  .DoCmd.RunSQL strSQL, dbFailOnError
  .CloseCurrentDatabase
 
End With
 
Set appAccess = Nothing
 MsgBox ("saved")
End Sub

[Ineedhelpplz]

I just want to use a space and underscore in line 9. Is it possible? How?

[twinnyfo]

No. Line 9-10 must look like this:

strSQL = "INSERT INTO [Finished Batches] ([ReleaseDate],[BatchSheetNo],[LotNumber],[BatchSize],[UOM],[BlenderNumber],[Ingr1], " & _
        "[Amt1],[Ing2],[Ing3],[Ing4],[Ing5],[Ing6],[Ing7],[Ing8],[Ing9],[Ing10],[Ing11],[Ing12],[Ing13],[Ing14],[Ing15],[Ing16],[Ing17],[Ing18],[Ing19],[Ing20],[Ing21],[Ing22],[Ing23],[Ing24],[Ing25],[Ing26],[Ing27],[Amt2],[Amt3],[Amt4],[Amt5],[Amt6],[Amt7],[Amt8],[Amt9],[Amt10],[Amt11],[Amt12],[Amt13],[Amt14],[Amt15],[Amt16],[Amt17],[Amt18],[Amt19],[Amt20],[Amt21],[Amt22],[Amt23],[Amt24],[Amt25],[Amt26],[Amt27],[Lot1a],[Lot2a],[Lot3a],[Lot4a],[Lot5a],[Lot6a],[Lot7a],[Lot8a],[Lot9a],[Lot10a],[Lot11a],[Lot12a],[Lot13a],[Lot14a],[Lot15a],[Lot16a],[Lot17a],[Lot18a],[Lot19a],[Lot20a],[Lot21a],[Lot22a],[Lot23a],[Lot24a],[Lot25a],[Lot26a],[Lot27a],[Amt1a],[Amt2a])" & _

Remember that a string, when interrupted, must be enclosed by Double Quotes.

[twinnyfo]

However, just looking at the width of your table and the number of fields, something tells me that your database is not fully normalized (see this topic for help: Database Normalization).

However, assuming that all your fields and data is necessary as is, the tips above should help you find a solution.

[Ineedhelpplz]

Thank you! I tried it so many ways but never put double quotes starting the new line. I'll read that info about normalization. Thanks to everyone!

[twinnyfo]

Great! I'm glad I could be of some service!

[NeoPa]

Just in case you're not sure why the double-quotes made the difference here - the continuation character must be between determinable sub-statements within a line. A string literal is not complete until it hits a terminating (single) double-quote character. Anything within a string literal is, by definition, within a sub-statement.

If you're curious as to why that would be the case then simply consider how complicated it would be to express certain values within a string if such character combinations were always treated as special characters instead of what you write in the string.

[Ineedhelpplz]

Thanks NeoPa, that makes sense.

[twinnyfo]

(whisper) Just so you know, NeoPa knows EVERYTHING!

:-)

[NeoPa]

More than happy to help. TwinnyFo did all the heavy lifting here. I just popped in to add a little snippet in the hope that it helps.

[Luuk]

nah, he was just repeating what I told to Ineedhelpplz in the first place....

;)

[NeoPa]

In a way you're right Luuk. Often in life though, it's not all about the answer - but much about leading someone towards being able to understand and work with that info.

Your contribution was helpful and important but TwinnyFo still did the heavy lifting in this case. He stuck with it and made it happen ;-)

Never worry that your original perfect answer is overlooked though. Most of the benefit that this site gives is to those users who look but never post. For them your contribution will surely prove very helpful for a long time to come.