Too many line continuations (1 Viewer)

StephenMelb

New member
Local time
Tomorrow, 04:24
Joined
May 18, 2020
Messages
4
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

I’m here to help
Staff member
Local time
Today, 09:24
Joined
Oct 29, 2018
Messages
13,739
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

New member
Local time
Tomorrow, 04:24
Joined
May 18, 2020
Messages
4
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

error reading drive A:
Local time
Tomorrow, 01:24
Joined
May 7, 2009
Messages
11,146
also you can:

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

Minty

AWF VIP
Local time
Today, 17:24
Joined
Jul 26, 2013
Messages
7,797
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.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:24
Joined
Jul 9, 2003
Messages
12,308
Three ways to build SQL Statements
I usually maintain that there are three ways to do everything, and MS Access is no exception! Even for a basic operation like building up a string in VBA code in MS Access there are three ways to do it. I wouldn't be surprised if there are more than three ways, but thinking like that would negate my rule of thumb:- "There are three ways to do everything" - It's a "Rule of Thumb" and it wouldn't be a "Rule of Thumb" if there weren't exceptions.

What are the three ways to build SQL Statements?
1) Continuation Character
2) Continuous Concatenation
3) Assign to Variable

Continuation Character
One way is to use the Continuation Character "_" however this does have a limit, and as you can see from this post, the OP quickly got to the limit and had problems.

Another way is:-
Continuous Concatenation
Basically you have a string variable to which you keep adding to "itself"

Continuous Concatenation is fine for simple operations, but if you plan on doing anything complicated like modifying your SQL statement to do multiple things then it might prove difficult.

Assign to Variable (See VBA Template below)
Hence I prefer having each single row of the SQL Statement assigned to its own variable.

SQL Statements 2 - Nifty Access

However this method has its own problems, the first one being that you need to create all the variables. There's a simple solution to this, you create a ready-made template you can paste directly into your MS Access VBA editor window with this often overlooked feature of MS Access:- "Insert File"

Insert Template into the VBA Editor Window

If you don't have access to YouTube then this is what you do:-

Select "Insert" from the Menu
then "File" from the drop-down list
find the folder with your templates in it.

I have a folder named My_MS_Access_Text_Insert_Folder which is where I keep all of my VBA templates.

Navigate to the text file with your particular VBA code template in and it is conveniently inserted into the VBA editor window.

Here is a copy of the template I use, you might want to add it to your "Insert Folder" right now! HOW? SEE MY BLOG HERE:- Add to "Insert File" Start building up your compendium of useful code for MS Access VBA...

Code:
Private Function fSQLX(strVar As String, lngVar As Long) As String
Dim strSubName As String
Dim strModuleName As String


Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "

strSubName = "fSQLX"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"

On Error GoTo Error_Handler

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim strSQL6 As String
Dim strSQL7 As String
Dim strSQL8 As String
Dim strSQL9 As String
Dim strSQL10 As String
Dim strSQL11 As String

''Format Text Correctly for the SQL Statement - Delimiters " "
'strVar1 = Chr(34) & strVar1 & Chr(34)

''Format Date Correctly for the SQL Statement - Delimiters # #
'strVar2 = Chr(35) & strVar2 & Chr(35)

strSQL1 = ""
strSQL2 = ""
strSQL3 = ""
strSQL4 = ""
strSQL5 = ""
strSQL6 = ""
strSQL7 = ""
strSQL8 = ""
strSQL9 = ""
strSQL10 = ""
strSQL11 = ""

strSQL0 = strSQL1 & strSQL2 & strSQL3 & strSQL4 & strSQL5 & strSQL6 & strSQL7 & strSQL8 & strSQL9 & strSQL10 & strSQL11

fSQLX = strSQL0

Exit_ErrorHandler:
   
    Exit Function

Error_Handler:  'Version - 1a
    Dim strErrFrom As String
    Dim strErrInfo As String
       
        strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
        strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
           
            Select Case Err.Number
                Case 0.123 'When Required, Replace Place Holder (1) with an Error Number
                    MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
                Case Else
                    MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
            End Select
        Resume Exit_ErrorHandler

End Function 'fSQLX

I have a blog:- SQL Statements For Beginners which may be of interest...
 
Last edited:

moke123

AWF VIP
Local time
Today, 12:24
Joined
Jan 11, 2013
Messages
2,219
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

CID VIP
Local time
Today, 17:24
Joined
Jan 14, 2017
Messages
14,255
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

I’m here to help
Staff member
Local time
Today, 09:24
Joined
Oct 29, 2018
Messages
13,739
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

Lifelong Learner
Local time
Today, 09:24
Joined
Mar 14, 2017
Messages
3,293
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.
 

Users who are viewing this thread

Top Bottom