Continue line of code on next line 2

[timotai]

HI

I have a long line of code which is so long is dropping onto the next line in the module. The code is an SQL query and so needs to be in a continuous line (as far as I know) so that the coding works otherwise it treats it as another line of coding. Is there any way I can tell access that this is all one line of code and to continue treating it as such even though it is split across 2 lines?

Any advise is greatly appreciated.

Many Thanks

Tim

 

[dpimental]

Use the "_" (underline) as in this.

Dim tblOrderEntry _
as String

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[timotai]

can't get access to accept that, maybe you could show me what you mean if I give you my bit of code:

StrSql1 = "SELECT DISTINCT [Holiday/Sickness_cover].Intance,WeekLog.[Employee Number]as emp1,[Holiday/Sickness_cover].[Hol/sick_shift], [Holiday/Sickness_cover].Cover_Shift, TeamMembers.Employee as emp2, TeamMembers.Active, TeamMembers.Coverflag FROM ((([Holiday/Sickness_cover] INNER JOIN (Shift_Patterns INNER JOIN WeekLog ON (Shift_Patterns.ShiftLevel = WeekLog.ShiftLevel) AND (Shift_Patterns.[ShiftPattern ID] = WeekLog.[ShiftPattern ID])) ON [Holiday/Sickness_cover].[Hol/sick_shift] = Shift_Patterns.unique) INNER JOIN Shift_Patterns AS Shift_Patterns_1 ON [Holiday/Sickness_cover].Cover_Shift = Shift_Patterns_1.unique) INNER JOIN WeekLog AS WeekLog_1 ON (Shift_Patterns_1.[ShiftPattern ID] = WeekLog_1.[ShiftPattern ID]) AND (Shift_Patterns_1.ShiftLevel = WeekLog_1.ShiftLevel))"
INNER JOIN TeamMembers ON WeekLog_1.[Employee Number] = TeamMembers.Employee WHERE (((WeekLog.[Employee Number])=" & rst.Fields("emp&quot & &quot And((TeamMembers.Active)=Yes)And((TeamMembers.Coverflag)Is Null))ORDER BY[Holiday/Sickness_cover].Intance;"

 

[dpimental]

Take each line, make sure that there is a space before the
closing quote and then add & _
Then start the next line with the opening quotes.

Try that.

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[timotai]

I figured it out now I had to put & _ and then it worked like this

StrSql1 = "SELECT DISTINCT [Holiday/Sickness_cover].Intance,WeekLog.[Employee Number]as emp1,[Holiday/Sickness_cover].[Hol/sick_shift], [Holiday/Sickness_cover].Cover_Shift, TeamMembers.Employee as emp2, TeamMembers.Active, TeamMembers.Coverflag FROM ((([Holiday/Sickness_cover] INNER JOIN (Shift_Patterns INNER JOIN WeekLog ON (Shift_Patterns.ShiftLevel = WeekLog.ShiftLevel) AND (Shift_Patterns.[ShiftPattern ID] = WeekLog.[ShiftPattern ID])) ON [Holiday/Sickness_cover].[Hol/sick_shift] = Shift_Patterns.unique) INNER JOIN Shift_Patterns AS Shift_Patterns_1 ON [Holiday/Sickness_cover].Cover_Shift = Shift_Patterns_1.unique) INNER JOIN WeekLog AS WeekLog_1 ON (Shift_Patterns_1.[ShiftPattern ID] = WeekLog_1.[ShiftPattern ID]) AND (Shift_Patterns_1.ShiftLevel = WeekLog_1.ShiftLevel)) " & _

"INNER JOIN TeamMembers ON WeekLog_1.[Employee Number] = TeamMembers.Employee WHERE (((WeekLog.[Employee Number])=" & rst.Fields("emp&quot & &quot And((TeamMembers.Active)=Yes)And((TeamMembers.Coverflag)Is Null))ORDER BY[Holiday/Sickness_cover].Intance;"

Cheers for the help and pointing in the right direction.

Tim

 

[timotai]

You replied the same time as me. funny! Im gonna give you a star though cus it was great help

tim

 

[dpimental]

Thanks. I love it when a plan comes together.

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[JeremyNYC]

Nice A-Team reference.

I always put the & at the start of the next line, so it's clear to me that it's a continuation of the line above it. Access knows no matter where it is, but that allows me to read it more easily. Just a personal preference.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Rungo]

What I would have done is create a String variable called strSQL. Next, I would've said something like:

strSQL = strSQL & "(First 60 characters of the SQL code)"
strSQL = strSQL & "(next 60 characters&quot

etc.

I'd follow this up at the end with strSQL.execute

Ken

 

[JeremyNYC]

That method certainly works fine, but it takes up a lot of space, and when you're wrapping lines because you're running out of space, seems rather counterproductive.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Databaseguy]

The _

Can be tricky at times. Make sure you don't forget commas between field names.

I've also used the:

strSQL = (60 lines)
strSQL - strSQL & (next 60 lines)

With success.