40

I'm using this insert statement in my code in VBA Excel, but I'm not able to break it into more than one line:

SqlQueryString = "Insert into Employee values(" & txtEmployeeNo.Value & " _
,'" & txtContractStartDate.Value & "' _
,'" & txtSeatNo.Value & "' _
,'" & txtFloor.Value & "','" & txtLeaves.Value & "')"

It is giving error "Expected end of statement". How can I fix it?

Jeff Schaller
  • 2,352
  • 5
  • 23
  • 38

3 Answers3

58

You cannot use the VB line-continuation character inside of a string.

SqlQueryString = "Insert into Employee values(" & txtEmployeeNo.Value & _
"','" & txtContractStartDate.Value &  _
"','" & txtSeatNo.Value & _
"','" & txtFloor.Value & "','" & txtLeaves.Value & "')"
DougM
  • 2,808
  • 17
  • 14
15

you may simply create your string in multiple steps, a bit redundant but it keeps the code readable and maintain sanity while debugging or editing

SqlQueryString = "Insert into Employee values(" 
SqlQueryString = SqlQueryString & txtEmployeeNo.Value & " ,"
SqlQueryString = SqlQueryString & " '" & txtEmployeeNo.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtContractStartDate.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtSeatNo.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtContractStartDate.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtSeatNo.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtFloor.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtLeaves.Value & "' )"
Georges Brisset
  • 246
  • 2
  • 6
4

If the long string to multiple lines confuses you. Then you may install mz-tools addin which is a freeware and has the utility which splits the line for you.

Download Mz-tools

If your string looks like below

SqlQueryString = "Insert into Employee values(" & txtEmployeeNo.Value & "','" & txtContractStartDate.Value & "','" & txtSeatNo.Value & "','" & txtFloor.Value & "','" & txtLeaves.Value & "')"

Simply select the string > right click on VBA IDE > Select MZ-tools > Split Lines

enter image description here

Santosh
  • 12,175
  • 4
  • 41
  • 72