Help: Continue String Code to next line

F

fhwa

ok, I'm trying to write a string code and currently the code works
however I've run out of room and I need to change some of the source
names it pulls from. Changing the source names create the string
longer and there is no room left. I need to know how I can split this
into two or more lines so I can update the code now and in the future.

strSQL = "SELECT tblEmployees.LastName, tblOutputs.OutputName,
tblBudgetedFigures.BudgetedTime, Sum(tblTimesheetEntries.Hours) AS
SumOfHours, [SumOfHours]/[BudgetedTime] AS PercentUsed,
tblSections.SectionID, tblSections.Section,
Sum(tblTimesheetEntries.TravelExpenses) AS SumOfTravelExpenses,
tblBudgetedFigures.TravelExpenses, IIf([Sumoftravelexpenses]=0,0,
[SumofTravelExpenses]/[tblbudgetedfigures].[TravelExpenses]) AS
PercentUsedTravel FROM tblSections INNER JOIN (tblOutputs INNER JOIN
((tblEmployees INNER JOIN tblBudgetedFigures ON
tblEmployees.EmployeeID = tblBudgetedFigures.EmployeeName) INNER JOIN
tblTimesheetEntries ON tblEmployees.EmployeeID =
tblTimesheetEntries.EmployeeID) ON (tblOutputs.OutputID =
tblTimesheetEntries.Output) AND (tblOutputs.OutputID =
tblBudgetedFigures.OutputName)) ON tblSections.SectionID =
tblEmployees.Section GROUP BY tblEmployees.LastName,
tblOutputs.OutputName, tblBudgetedFigures.BudgetedTime,
tblSections.SectionID, tblSections.Section,
tblBudgetedFigures.TravelExpenses "

I hope someone can help, I've run out of ideas.
 
J

Jon Skeet [C# MVP]

fhwa said:
ok, I'm trying to write a string code and currently the code works
however I've run out of room and I need to change some of the source
names it pulls from. Changing the source names create the string
longer and there is no room left. I need to know how I can split this
into two or more lines so I can update the code now and in the future.

strSQL = "SELECT tblEmployees.LastName, tblOutputs.OutputName,
tblBudgetedFigures.BudgetedTime, Sum(tblTimesheetEntries.Hours) AS
SumOfHours, [SumOfHours]/[BudgetedTime] AS PercentUsed,
tblSections.SectionID, tblSections.Section,
Sum(tblTimesheetEntries.TravelExpenses) AS SumOfTravelExpenses,
tblBudgetedFigures.TravelExpenses, IIf([Sumoftravelexpenses]=0,0,
[SumofTravelExpenses]/[tblbudgetedfigures].[TravelExpenses]) AS
PercentUsedTravel FROM tblSections INNER JOIN (tblOutputs INNER JOIN
((tblEmployees INNER JOIN tblBudgetedFigures ON
tblEmployees.EmployeeID = tblBudgetedFigures.EmployeeName) INNER JOIN
tblTimesheetEntries ON tblEmployees.EmployeeID =
tblTimesheetEntries.EmployeeID) ON (tblOutputs.OutputID =
tblTimesheetEntries.Output) AND (tblOutputs.OutputID =
tblBudgetedFigures.OutputName)) ON tblSections.SectionID =
tblEmployees.Section GROUP BY tblEmployees.LastName,
tblOutputs.OutputName, tblBudgetedFigures.BudgetedTime,
tblSections.SectionID, tblSections.Section,
tblBudgetedFigures.TravelExpenses "

I hope someone can help, I've run out of ideas.
Click to expand...

Do you mean verbatim string literals (with an @ prefix)?

string x = @"First line
second line
third line";
 
F

fhwa

sorry, I don't really know what you mean. I'm fairly new to the whole
VBA coding and am updating some access databases for my office that
has this code. The code above currently runs all in a single line
which runs out of room and doesn't allow me to add more because of the
character limit. For example, I need to change tblOutputs to
tblOutputs2008, thus making the line longer however it doesn't allow
me to. What I ultimately need to do is figure out a way to split the
code onto two separate lines and have the code continue to work and
read from the end of the first line and continue at the beginning of
the second. I hope this description helps. Thanks
 
C

Chris Dunaway

sorry, I don't really know what you mean. I'm fairly new to the whole
VBA coding and am updating some access databases for my office that
has this code. The code above currently runs all in a single line
which runs out of room and doesn't allow me to add more because of the
character limit. For example, I need to change tblOutputs to
tblOutputs2008, thus making the line longer however it doesn't allow
me to. What I ultimately need to do is figure out a way to split the
code onto two separate lines and have the code continue to work and
read from the end of the first line and continue at the beginning of
the second. I hope this description helps. Thanks
Click to expand...

This is a C# newsgroup, not a VB one, so you will need to ask in an
appropriate group.

Chris
 
G

Graeme Bradbury

Use table aliases in the statement.
ie.

"SELECT tblEmployees.LastName, tblOutputs.OutputName,
tblBudgetedFigures.BudgetedTime, Sum(tblTimesheetEntries.Hours) AS
SumOfHours, [SumOfHours]/[BudgetedTime] AS PercentUsed,
s.SectionID, s.Section,
Sum(tblTimesheetEntries.TravelExpenses) AS SumOfTravelExpenses,
tblBudgetedFigures.TravelExpenses, IIf([Sumoftravelexpenses]=0,0,
[SumofTravelExpenses]/[tblbudgetedfigures].[TravelExpenses]) AS
PercentUsedTravel FROM tblSections s INNER JOIN (tblOutputs INNER JOIN
((tblEmployees INNER JOIN tblBudgetedFigures ON
tblEmployees.EmployeeID = tblBudgetedFigures.EmployeeName) INNER JOIN
tblTimesheetEntries ON tblEmployees.EmployeeID =
tblTimesheetEntries.EmployeeID) ON (tblOutputs.OutputID =
tblTimesheetEntries.Output) AND (tblOutputs.OutputID =
tblBudgetedFigures.OutputName)) ON s.SectionID =
tblEmployees.Section GROUP BY tblEmployees.LastName,
tblOutputs.OutputName, tblBudgetedFigures.BudgetedTime,
tblSections.SectionID, s.Section,
tblBudgetedFigures.TravelExpenses "

Basically in a FROM clause or JOIN clause type table alias then you can use
alias.Column instead of table.Column that should save you a few characters
 
Top