Too many line continuations

[StephenMelb]

Hello,
I have hit the too many line continuations in a vba select query. I have read you are limited to 24 lines and to get around that you can trick access by various methods that I have not been able to make work. I tried this cstrOldSQL = cstrOldSQL + "SELECT Contract.Accommodation," & _ but this fails.

I have a table called contracts and then I have a summary form based on query where user selects from drop down list the "Artist". The form then displays all contracts made for that specific Artist.

A double click on the Artist contract number then opens the contract form for that Artist. But I am unable to include all the field data for the form as I hit the limit of line continuations. Here is what it looks like ...

Private Sub Form_Load() ' Opens Contract form and populates fields for that record

Dim cstrOldSQL As String

cstrOldSQL = "SELECT Contract.Artist, " & _
"Contract.ContractNo, " & _
"Contract.fkTourID, " & _
"Contract.[Agreement Date], " & _
"Contract.[Date Sent], " & _
"Contract.[Behalf Of], " & _
"Contract.Employer, " & _
"Contract.Venue, " & _
"Contract.[Show Date1], " & _
"Contract.[Show Date2], " & _
"Contract.[Show Date3], " & _
"Contract.[Show Date4], " & _
"Contract.[Approx Show Times], " & _
"Contract.Fee, " & _
"Contract.[Ticket Price], " & _
"Contract.[Method of Payment], " & _
"Contract.Deposit, " & _
"Contract.[Deposit Paid], " & _
"Contract.Accommodation, " & _
"Contract.Airfares, " & _
"Contract.Motel, " & _
"Contract.[Agent for Venue], " & _
"Contract.[Agent Address] " & _
"FROM Contract " & _
"WHERE ((Contract.Artist) = (Forms!frmContractsArtistSummary!Artist))"


Me.RecordSource = cstrOldSQL
Me.Requery
End Sub

Any help would be greatly appreciated and THANKS FOR READING.

 

[theDBguy]

Hi. If the limit is the number of lines, then you could try reducing them. For example, instead of:

select field1, & _
field2, & _
field3 & _
etc.

You could try:

select field1, field2, field3, & _
field4, etc.

 

[StephenMelb]

You know I was sure I had tried that and failed. But no I hadn't and it works perfectly.

Thank you, Thank you. I have been trying to work this out for days as I didn't want to waste anyones time here....

Such a simple fix.

 

[arnelgp]

also you can:

cstrOldSQL = cstrOldSQL & "SELECT Contract.Artist, "
cstrOldSQL = cstrOldSQL & "Contract.ContractNo, "
cstrOldSQL = cstrOldSQL & ...

 

[Minty]

Just for general information, I think it baulks at anything over 15 continuation lines.
I tend to use the same construct as @arnelgp although it is slightly more typing I find it easier to edit and debug.

 

[moke123]

I tried this cstrOldSQL = cstrOldSQL + "SELECT Contract.Accommodation," & _ but this fails.

Just want to point out something else which is the difference between + and & when concatenating.
Generally you would use the amphersand (&). If you use a plus sign(+) and one side of the equation is null, it will return null.
There are times where you would want to use a plus sign. See "The law of propagating nulls" found here . . . LOPN

 

[isladogs]

There are certainly even more than three ways...

For long strings, I list the SQL separately for the separate clauses and concatenate those. For example:
StrSQL = strSELECT & strFROM & strWHERE & strORDERBY & ";"

 

[theDBguy]

You know I was sure I had tried that and failed. But no I hadn't and it works perfectly.

Thank you, Thank you. I have been trying to work this out for days as I didn't want to waste anyones time here....

Such a simple fix.

Hi. Glad to hear you got it sorted out. As you can see, that was just one of many ways to fix your problem. Good luck with your project.

 

[Isaac]

Offering what probably wouldn't be considered a 4th way, but rather a total alternative to having all that messy VBA sql combo's ….. Something I've become quite fond of doing.
Save a query with everything just the way you want it, except "placeholder" values in the appropriate place.
Use vba to manipulate the Currentdb.Querydefs("QueryName").SQL property. (You can always read it back for assignment to a vba variable of course, too).
Saves a lot of messy code that might be hard for the next guy to read.