No more space on line of code?

K

Kevin

In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy, and
as best I know the SQL statement has to be all on one line. Is there a way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to quantity
of characters or quantity of objects? With all of Access's capabilities, it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax, Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" , ""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" & _
 
B

Brendan Reynolds

strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

You can also do something like ...

strSQL = SELECT SomeField "
strSQL = strSQL & " FROM SomeTable"

And of course you can mix the two techniques ...

strSQL = "SELECT SomeField " & _
"FROM SomeTable "

strSQL = strSQL & "WHERE SomeCondition " & _
"ORDER BY Whatever"

--
Brendan Reynolds
Access MVP

Kevin said:
In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy,
and
as best I know the SQL statement has to be all on one line. Is there a
way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to
quantity
of characters or quantity of objects? With all of Access's capabilities,
it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" ,
""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" &
_
Click to expand...
 
K

Kevin

I don't know that my initial question was understood. Your first example is
how my code is laid out, but the problem is that the length of the first line
"strSQL = ..." is so long that I can no longer type any more fields. It's
like it's at its maximum.

Or...am I to percieve your response as I should repeat the following
"series" one time for each field?
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

Thanks again!
Kevin

Brendan Reynolds said:
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

You can also do something like ...

strSQL = SELECT SomeField "
strSQL = strSQL & " FROM SomeTable"

And of course you can mix the two techniques ...

strSQL = "SELECT SomeField " & _
"FROM SomeTable "

strSQL = strSQL & "WHERE SomeCondition " & _
"ORDER BY Whatever"

--
Brendan Reynolds
Access MVP

Kevin said:
In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy,
and
as best I know the SQL statement has to be all on one line. Is there a
way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to
quantity
of characters or quantity of objects? With all of Access's capabilities,
it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" ,
""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" &
_
Click to expand...
Click to expand...
 
J

JohnV

Hello Kevin,

You can do as Brendan suggested. Here is how it can work:

strSQL = "SELECT and then enter text for line 1... " _
& "continue the SELECT statement text for line 2... " _
& "FROM clause here " _
& "WHERE Clause here;"

Be sure that there is a space before the double-quote at the end of the line.

John

Kevin said:
I don't know that my initial question was understood. Your first example is
how my code is laid out, but the problem is that the length of the first line
"strSQL = ..." is so long that I can no longer type any more fields. It's
like it's at its maximum.

Or...am I to percieve your response as I should repeat the following
"series" one time for each field?
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

Thanks again!
Kevin

Brendan Reynolds said:
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

You can also do something like ...

strSQL = SELECT SomeField "
strSQL = strSQL & " FROM SomeTable"

And of course you can mix the two techniques ...

strSQL = "SELECT SomeField " & _
"FROM SomeTable "

strSQL = strSQL & "WHERE SomeCondition " & _
"ORDER BY Whatever"

--
Brendan Reynolds
Access MVP

Kevin said:
In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy,
and
as best I know the SQL statement has to be all on one line. Is there a
way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to
quantity
of characters or quantity of objects? With all of Access's capabilities,
it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" ,
""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" &
_
Click to expand...
Click to expand...
Click to expand...
 
K

Kevin

That makes sense to me now!
Thank you for the clarification!!!
Kevin

JohnV said:
Hello Kevin,

You can do as Brendan suggested. Here is how it can work:

strSQL = "SELECT and then enter text for line 1... " _
& "continue the SELECT statement text for line 2... " _
& "FROM clause here " _
& "WHERE Clause here;"

Be sure that there is a space before the double-quote at the end of the line.

John

Kevin said:
I don't know that my initial question was understood. Your first example is
how my code is laid out, but the problem is that the length of the first line
"strSQL = ..." is so long that I can no longer type any more fields. It's
like it's at its maximum.

Or...am I to percieve your response as I should repeat the following
"series" one time for each field?
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

Thanks again!
Kevin

Brendan Reynolds said:
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

You can also do something like ...

strSQL = SELECT SomeField "
strSQL = strSQL & " FROM SomeTable"

And of course you can mix the two techniques ...

strSQL = "SELECT SomeField " & _
"FROM SomeTable "

strSQL = strSQL & "WHERE SomeCondition " & _
"ORDER BY Whatever"

--
Brendan Reynolds
Access MVP

In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy,
and
as best I know the SQL statement has to be all on one line. Is there a
way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to
quantity
of characters or quantity of objects? With all of Access's capabilities,
it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" ,
""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" &
_
Click to expand...
Click to expand...
Click to expand...
 
D

David C. Holley

break it and concatonate(sp)

strSQL = ""
strSQL = strSQL & "SELECT me, you, them, us, we, "
strSQL = strSQL & "I, he, she, it, "
In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy, and
as best I know the SQL statement has to be all on one line. Is there a way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to quantity
of characters or quantity of objects? With all of Access's capabilities, it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax, Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" , ""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" & _
Click to expand...
 
D

David C. Holley

What we're saying is BREAK UP the statement from one line of code into
multiple line*s* of code.
I don't know that my initial question was understood. Your first example is
how my code is laid out, but the problem is that the length of the first line
"strSQL = ..." is so long that I can no longer type any more fields. It's
like it's at its maximum.

Or...am I to percieve your response as I should repeat the following
"series" one time for each field?
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

Thanks again!
Kevin

:

strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

You can also do something like ...

strSQL = SELECT SomeField "
strSQL = strSQL & " FROM SomeTable"

And of course you can mix the two techniques ...

strSQL = "SELECT SomeField " & _
"FROM SomeTable "

strSQL = strSQL & "WHERE SomeCondition " & _
"ORDER BY Whatever"

--
Brendan Reynolds
Access MVP

In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy,
and
as best I know the SQL statement has to be all on one line. Is there a
way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to
quantity
of characters or quantity of objects? With all of Access's capabilities,
it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" ,
""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" &
_
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Ferguson

It is lengthy, and
as best I know the SQL statement has to be all on one line.
Click to expand...

1 - the SQL definitely does not have to be all on one line, and is really
much more human-legible if it's broken up

2 - coding the string can be enormously simplified with a bit of
structured programming vis:

dim cmd as new CSQLCommand

with cmd
.Add "SELECT"
.Add " Project.ProjectName, "
.Add " Bid.BidNumber, "
.Add " Item.RoomNumber & "" - "" & Item.ItemNumber AS ItemLabel,"
.Add " Item.RoomNumber, "
.Add " Item.ItemNumber, "
.Add " and all the other fields etc etc
.Add "FROM Project"
.Add " LEFT JOIN Bid "
.Add " ON Project.ProjectNum = Bid.OwnerProject"
.Add " and so on "

end with

' final check is vital with complex commands
debug.assert vbYes=msgBox(cmd.SQL,vbYesNo,"Is this okay?")

' then do the thing
Set rs = cmd.ReturnRecordset



Creating the CSQLCommand class is left as an exercise for the reader, but
it shouldn't take much more than fifteen minutes, completely debugged.

Hope that helps


Tim F
 
R

RD

While what the others have posted will help you, it won't help you understand
*what* they're doing.

Brendan's example uses the "_" (underscore) character, with a leading Space, at
the end of each line. It's a line continuation symbol that allows you to put a
long line of code on more than one line. It is so basic to coding in the VB
environment that I'm having a hard time finding a reference to it in Help but
here's a good link:
http://www.fontstuff.com/access/acctut17.htm

David's example of breaking it up and concatenating it is the method I use
myself. Both methods work well. The concatenation method allows you to step
through the code and check the value of your SQL string as you build it. I find
that helpful when tracking down errors in my rather dubious SQL writing.

HTH,
RD


In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy, and
as best I know the SQL statement has to be all on one line. Is there a way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to quantity
of characters or quantity of objects? With all of Access's capabilities, it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax, Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" , ""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" & _
Click to expand...
 
S

Stephen Lebans

Quite presumptuous of you to state:
"While what the others have posted will help you, it won't help you
understand *what* they're doing."

Perhaps you did not understant the OP's following response.
That makes sense to me now!
Thank you for the clarification!!!
Kevin
Click to expand...


--
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


RD said:
While what the others have posted will help you, it won't help you
understand
*what* they're doing.

Brendan's example uses the "_" (underscore) character, with a leading
Space, at
the end of each line. It's a line continuation symbol that allows you to
put a
long line of code on more than one line. It is so basic to coding in the
VB
environment that I'm having a hard time finding a reference to it in Help
but
here's a good link:
http://www.fontstuff.com/access/acctut17.htm

David's example of breaking it up and concatenating it is the method I use
myself. Both methods work well. The concatenation method allows you to
step
through the code and check the value of your SQL string as you build it.
I find
that helpful when tracking down errors in my rather dubious SQL writing.

HTH,
RD


In pasting SQL into a line of code in a form's VBA code, I have reached a
point where it will no longer accept any more characters. It is lengthy,
and
as best I know the SQL statement has to be all on one line. Is there a
way
around this? I tried hitting "Enter" and then continuing on the following
line, but that started resulting in errors. Is each line limited to
quantity
of characters or quantity of objects? With all of Access's capabilities,
it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & "" ,
""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor" &
_
Click to expand...
Click to expand...
 
B

Brendan Reynolds

Sorry Kevin, I guess I was a bit cryptic. I see you've got lots of
clarification from others while I was sleeping though! :)

About the only thing that I can add is that when using the line continuation
characters (space plus underscore) to break a line of code within a string
literal such as your SQL statement you have to be careful about the
placement of quotes. This will work ...

strSQL = "SELECT SomeField " & _
"FROM SomeTable"

.... but this won't ...

strSQL = "SELECT SomeField & _
FROM SomeTable"

--
Brendan Reynolds
Access MVP

Kevin said:
I don't know that my initial question was understood. Your first example
is
how my code is laid out, but the problem is that the length of the first
line
"strSQL = ..." is so long that I can no longer type any more fields. It's
like it's at its maximum.

Or...am I to percieve your response as I should repeat the following
"series" one time for each field?
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

Thanks again!
Kevin

Brendan Reynolds said:
strSQL = "SELECT SomeField " & _
"FROM SomeTable " & _
"WHERE SomeCondition " & _
"ORDER BY Whatever"

You can also do something like ...

strSQL = SELECT SomeField "
strSQL = strSQL & " FROM SomeTable"

And of course you can mix the two techniques ...

strSQL = "SELECT SomeField " & _
"FROM SomeTable "

strSQL = strSQL & "WHERE SomeCondition " & _
"ORDER BY Whatever"

--
Brendan Reynolds
Access MVP

Kevin said:
In pasting SQL into a line of code in a form's VBA code, I have reached
a
point where it will no longer accept any more characters. It is
lengthy,
and
as best I know the SQL statement has to be all on one line. Is there a
way
around this? I tried hitting "Enter" and then continuing on the
following
line, but that started resulting in errors. Is each line limited to
quantity
of characters or quantity of objects? With all of Access's
capabilities,
it
doesn't seem there should be a limit like this.

I've pasted the SQL statement below in case there is a way to alter it
to
fit it on the line. Any feed back is greatly appreciated!

strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity,
ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost,
ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType, Project.SiteStreetAddress, [SiteCity] & ""
,
""
& [SiteState] & "" "" & [SiteZip] AS SiteCityState, Product.CBDCode,
[Quantity]*[MaterialCost] AS LineMaterialCost,
[Quantity]*([MachineLaborHours]+[BuildingLaborHours]) AS
LineTotalLabor" &
_
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trouble with Sum on Report 3

Top