Line-Break VBA to pass to a Query

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I know my SQL works. I used SQL to VBA to convert it to VBA, but now part of
the string so too long in the VBE that it causes and error. Here's the SQL:
strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k, tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American, tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND
((tblStocksGroup.HDVest50k) Like
IIf([Forms]![frmMaster]![chkVest50K]=-1,""*"",""HDVest50K"")) AND
((tblStocksGroup.HDVetst100k) Like
IIf([Forms]![frmMaster]![chkHDVetst100k]=-1,""*"",""HDVetst100k"")) AND
((tblStocksGroup.ETF) Like
IIf([Forms]![frmMaster]![chkETF]=-1,""*"",""ETF"")) AND
((tblStocksGroup.NetJets) Like
IIf([Forms]![frmMaster]![chkNetJets]=-1,""*"",""NetJets"")) AND
((tblStocksGroup.JetBlue) Like
IIf([Forms]![frmMaster]![chkJetBlue]=-1,""*"",""JetBlue"")) AND
((tblStocksGroup.JetBlueTradeLink) Like
IIf([Forms]![frmMaster]![chkJetBlueTradeLink]=-1,""*"",""JetBlueTradeLink""))
AND ((tblStocksGroup.AirWisconsin) Like
IIf([Forms]![frmMaster]![chkAirWisconsin]=-1,""*"",""AirWisconsin"")) AND
((tblStocksGroup.Alaska) Like
IIf([Forms]![frmMaster]![chkAlaska]=-1,""*"",""Alaska"")) AND
((tblStocksGroup.American) Like
IIf([Forms]![frmMaster]![chkAmerican]=-1,""*"",""American"")) AND
((tblStocksGroup.Continental) Like
IIf([Forms]![frmMaster]![chkContinental]=-1,""*"",""Continental"")) AND
((tblStocksGroup.Delta) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Frontier) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Southwest) Like
IIf([Forms]![frmMaster]![chkSouthwest]=-1,""*"",""Southwest"")) AND
((tblStocksGroup.UnitedFAS) Like
IIf([Forms]![frmMaster]![chkUnitedFAS]=-1,""*"",""UnitedFAS"")) AND
((tblStocksGroup.UnitedPilots) Like
IIf([Forms]![frmMaster]![chkUnitedPilots]=-1,""*"",""UnitedPilots"")) AND
((tblStocksGroup.WorldAirway) Like
IIf([Forms]![frmMaster]![chkWorldAirway]=-1,""*"",""WorldAirway"")) AND
((tblStocksGroup.LeveragedLong) Like
IIf([Forms]![frmMaster]![chkLeveragedLong]=-1,""*"",""LeveragedLong"")) AND
((tblStocksGroup.LeveragedShort) Like
IIf([Forms]![frmMaster]![chkLeveragedShort]=-1,""*"",""LeveragedShort"")) AND
((tblStocksGroup.Other1) Like
IIf([Forms]![frmMaster]![chkLeveragedOther1]=-1,""*"",""LeveragedOther1""))
AND ((tblStocksGroup.Other2) Like
IIf([Forms]![frmMaster]![chkLeveragedOther2]=-1,""*"",""LeveragedOther2"")))
" & vbCrLf & _
"ORDER BY SharePrices.DateTime;"

Where can I put in logical breaks to I can make this work in the VBE? I
tried this:
& vbCrLf & _
Tried it before the 'Like' and I tried before the 'AND'; nothing has worked
thus far. There must e a pretty easy way to break this up though, right.

Appreciate any help with this.

Thanks!
Ryan--
 
Try breaking it up like

strSQL = ""SELECT ..."
strSQL = strSQL & " ..."
strSQL = strSQL & " ...."

until you've built up the whole string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ryguy7272 said:
I know my SQL works. I used SQL to VBA to convert it to VBA, but now part
of
the string so too long in the VBE that it causes and error. Here's the
SQL:
strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k,
tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American,
tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1,
tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND
((tblStocksGroup.HDVest50k) Like
IIf([Forms]![frmMaster]![chkVest50K]=-1,""*"",""HDVest50K"")) AND
((tblStocksGroup.HDVetst100k) Like
IIf([Forms]![frmMaster]![chkHDVetst100k]=-1,""*"",""HDVetst100k"")) AND
((tblStocksGroup.ETF) Like
IIf([Forms]![frmMaster]![chkETF]=-1,""*"",""ETF"")) AND
((tblStocksGroup.NetJets) Like
IIf([Forms]![frmMaster]![chkNetJets]=-1,""*"",""NetJets"")) AND
((tblStocksGroup.JetBlue) Like
IIf([Forms]![frmMaster]![chkJetBlue]=-1,""*"",""JetBlue"")) AND
((tblStocksGroup.JetBlueTradeLink) Like
IIf([Forms]![frmMaster]![chkJetBlueTradeLink]=-1,""*"",""JetBlueTradeLink""))
AND ((tblStocksGroup.AirWisconsin) Like
IIf([Forms]![frmMaster]![chkAirWisconsin]=-1,""*"",""AirWisconsin"")) AND
((tblStocksGroup.Alaska) Like
IIf([Forms]![frmMaster]![chkAlaska]=-1,""*"",""Alaska"")) AND
((tblStocksGroup.American) Like
IIf([Forms]![frmMaster]![chkAmerican]=-1,""*"",""American"")) AND
((tblStocksGroup.Continental) Like
IIf([Forms]![frmMaster]![chkContinental]=-1,""*"",""Continental"")) AND
((tblStocksGroup.Delta) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Frontier) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Southwest) Like
IIf([Forms]![frmMaster]![chkSouthwest]=-1,""*"",""Southwest"")) AND
((tblStocksGroup.UnitedFAS) Like
IIf([Forms]![frmMaster]![chkUnitedFAS]=-1,""*"",""UnitedFAS"")) AND
((tblStocksGroup.UnitedPilots) Like
IIf([Forms]![frmMaster]![chkUnitedPilots]=-1,""*"",""UnitedPilots"")) AND
((tblStocksGroup.WorldAirway) Like
IIf([Forms]![frmMaster]![chkWorldAirway]=-1,""*"",""WorldAirway"")) AND
((tblStocksGroup.LeveragedLong) Like
IIf([Forms]![frmMaster]![chkLeveragedLong]=-1,""*"",""LeveragedLong""))
AND
((tblStocksGroup.LeveragedShort) Like
IIf([Forms]![frmMaster]![chkLeveragedShort]=-1,""*"",""LeveragedShort""))
AND
((tblStocksGroup.Other1) Like
IIf([Forms]![frmMaster]![chkLeveragedOther1]=-1,""*"",""LeveragedOther1""))
AND ((tblStocksGroup.Other2) Like
IIf([Forms]![frmMaster]![chkLeveragedOther2]=-1,""*"",""LeveragedOther2"")))
" & vbCrLf & _
"ORDER BY SharePrices.DateTime;"

Where can I put in logical breaks to I can make this work in the VBE? I
tried this:
& vbCrLf & _
Tried it before the 'Like' and I tried before the 'AND'; nothing has
worked
thus far. There must e a pretty easy way to break this up though, right.

Appreciate any help with this.

Thanks!
Ryan--
Click to expand...
 
I don't think vbCrLf is a valid character inside an SQL string, and you are
contencating them into the string. What exactly is the problem you're
getting? Too many characters in one line for the actual VBE? Rather than
and vbCrLf, try the line continutation _
or break them into seperate parts and contencate each part into the variable
(the string certainly is nowhere near too long to fit in a var)

strSQL = "SELECT blah blah blah "
strSQL = strSQL & "blah blah blah blah "
strSQL = strSQL & "blah blah blah blah "
strSQL = strSQL & "blah blah blah blah "
etc etc.

Stuart McCall has a handy converted (based off an idea by Allen Browne) for
doing this automatically...

http://www.smccall.demon.co.uk/Downloads.htm#SQLFormat

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



ryguy7272 said:
I know my SQL works. I used SQL to VBA to convert it to VBA, but now part of
the string so too long in the VBE that it causes and error. Here's the SQL:
strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k, tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American, tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND
((tblStocksGroup.HDVest50k) Like
IIf([Forms]![frmMaster]![chkVest50K]=-1,""*"",""HDVest50K"")) AND
((tblStocksGroup.HDVetst100k) Like
IIf([Forms]![frmMaster]![chkHDVetst100k]=-1,""*"",""HDVetst100k"")) AND
((tblStocksGroup.ETF) Like
IIf([Forms]![frmMaster]![chkETF]=-1,""*"",""ETF"")) AND
((tblStocksGroup.NetJets) Like
IIf([Forms]![frmMaster]![chkNetJets]=-1,""*"",""NetJets"")) AND
((tblStocksGroup.JetBlue) Like
IIf([Forms]![frmMaster]![chkJetBlue]=-1,""*"",""JetBlue"")) AND
((tblStocksGroup.JetBlueTradeLink) Like
IIf([Forms]![frmMaster]![chkJetBlueTradeLink]=-1,""*"",""JetBlueTradeLink""))
AND ((tblStocksGroup.AirWisconsin) Like
IIf([Forms]![frmMaster]![chkAirWisconsin]=-1,""*"",""AirWisconsin"")) AND
((tblStocksGroup.Alaska) Like
IIf([Forms]![frmMaster]![chkAlaska]=-1,""*"",""Alaska"")) AND
((tblStocksGroup.American) Like
IIf([Forms]![frmMaster]![chkAmerican]=-1,""*"",""American"")) AND
((tblStocksGroup.Continental) Like
IIf([Forms]![frmMaster]![chkContinental]=-1,""*"",""Continental"")) AND
((tblStocksGroup.Delta) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Frontier) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Southwest) Like
IIf([Forms]![frmMaster]![chkSouthwest]=-1,""*"",""Southwest"")) AND
((tblStocksGroup.UnitedFAS) Like
IIf([Forms]![frmMaster]![chkUnitedFAS]=-1,""*"",""UnitedFAS"")) AND
((tblStocksGroup.UnitedPilots) Like
IIf([Forms]![frmMaster]![chkUnitedPilots]=-1,""*"",""UnitedPilots"")) AND
((tblStocksGroup.WorldAirway) Like
IIf([Forms]![frmMaster]![chkWorldAirway]=-1,""*"",""WorldAirway"")) AND
((tblStocksGroup.LeveragedLong) Like
IIf([Forms]![frmMaster]![chkLeveragedLong]=-1,""*"",""LeveragedLong"")) AND
((tblStocksGroup.LeveragedShort) Like
IIf([Forms]![frmMaster]![chkLeveragedShort]=-1,""*"",""LeveragedShort"")) AND
((tblStocksGroup.Other1) Like
IIf([Forms]![frmMaster]![chkLeveragedOther1]=-1,""*"",""LeveragedOther1""))
AND ((tblStocksGroup.Other2) Like
IIf([Forms]![frmMaster]![chkLeveragedOther2]=-1,""*"",""LeveragedOther2"")))
" & vbCrLf & _
"ORDER BY SharePrices.DateTime;"

Where can I put in logical breaks to I can make this work in the VBE? I
tried this:
& vbCrLf & _
Tried it before the 'Like' and I tried before the 'AND'; nothing has worked
thus far. There must e a pretty easy way to break this up though, right.

Appreciate any help with this.

Thanks!
Ryan--
Click to expand...
 
try the line continutation _

Now that I think of it, there's a limit for the number of continuations you
can have on one line, though I forget what it is. If memory serves me
somewhat correctly, I think this may be too long to use continuations on
(assuming that you don't exceed the standard 80chars per line of code rule).


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



Jack Leach said:
I don't think vbCrLf is a valid character inside an SQL string, and you are
contencating them into the string. What exactly is the problem you're
getting? Too many characters in one line for the actual VBE? Rather than
and vbCrLf, try the line continutation _
or break them into seperate parts and contencate each part into the variable
(the string certainly is nowhere near too long to fit in a var)

strSQL = "SELECT blah blah blah "
strSQL = strSQL & "blah blah blah blah "
strSQL = strSQL & "blah blah blah blah "
strSQL = strSQL & "blah blah blah blah "
etc etc.

Stuart McCall has a handy converted (based off an idea by Allen Browne) for
doing this automatically...

http://www.smccall.demon.co.uk/Downloads.htm#SQLFormat

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



ryguy7272 said:
I know my SQL works. I used SQL to VBA to convert it to VBA, but now part of
the string so too long in the VBE that it causes and error. Here's the SQL:
strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k, tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American, tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND
((tblStocksGroup.HDVest50k) Like
IIf([Forms]![frmMaster]![chkVest50K]=-1,""*"",""HDVest50K"")) AND
((tblStocksGroup.HDVetst100k) Like
IIf([Forms]![frmMaster]![chkHDVetst100k]=-1,""*"",""HDVetst100k"")) AND
((tblStocksGroup.ETF) Like
IIf([Forms]![frmMaster]![chkETF]=-1,""*"",""ETF"")) AND
((tblStocksGroup.NetJets) Like
IIf([Forms]![frmMaster]![chkNetJets]=-1,""*"",""NetJets"")) AND
((tblStocksGroup.JetBlue) Like
IIf([Forms]![frmMaster]![chkJetBlue]=-1,""*"",""JetBlue"")) AND
((tblStocksGroup.JetBlueTradeLink) Like
IIf([Forms]![frmMaster]![chkJetBlueTradeLink]=-1,""*"",""JetBlueTradeLink""))
AND ((tblStocksGroup.AirWisconsin) Like
IIf([Forms]![frmMaster]![chkAirWisconsin]=-1,""*"",""AirWisconsin"")) AND
((tblStocksGroup.Alaska) Like
IIf([Forms]![frmMaster]![chkAlaska]=-1,""*"",""Alaska"")) AND
((tblStocksGroup.American) Like
IIf([Forms]![frmMaster]![chkAmerican]=-1,""*"",""American"")) AND
((tblStocksGroup.Continental) Like
IIf([Forms]![frmMaster]![chkContinental]=-1,""*"",""Continental"")) AND
((tblStocksGroup.Delta) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Frontier) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Southwest) Like
IIf([Forms]![frmMaster]![chkSouthwest]=-1,""*"",""Southwest"")) AND
((tblStocksGroup.UnitedFAS) Like
IIf([Forms]![frmMaster]![chkUnitedFAS]=-1,""*"",""UnitedFAS"")) AND
((tblStocksGroup.UnitedPilots) Like
IIf([Forms]![frmMaster]![chkUnitedPilots]=-1,""*"",""UnitedPilots"")) AND
((tblStocksGroup.WorldAirway) Like
IIf([Forms]![frmMaster]![chkWorldAirway]=-1,""*"",""WorldAirway"")) AND
((tblStocksGroup.LeveragedLong) Like
IIf([Forms]![frmMaster]![chkLeveragedLong]=-1,""*"",""LeveragedLong"")) AND
((tblStocksGroup.LeveragedShort) Like
IIf([Forms]![frmMaster]![chkLeveragedShort]=-1,""*"",""LeveragedShort"")) AND
((tblStocksGroup.Other1) Like
IIf([Forms]![frmMaster]![chkLeveragedOther1]=-1,""*"",""LeveragedOther1""))
AND ((tblStocksGroup.Other2) Like
IIf([Forms]![frmMaster]![chkLeveragedOther2]=-1,""*"",""LeveragedOther2"")))
" & vbCrLf & _
"ORDER BY SharePrices.DateTime;"

Where can I put in logical breaks to I can make this work in the VBE? I
tried this:
& vbCrLf & _
Tried it before the 'Like' and I tried before the 'AND'; nothing has worked
thus far. There must e a pretty easy way to break this up though, right.

Appreciate any help with this.

Thanks!
Ryan--
Click to expand...
Click to expand...
 
Jack Leach said:
Now that I think of it, there's a limit for the number of continuations
you
can have on one line, though I forget what it is. If memory serves me
Click to expand...

The number is fixed and unchangeable (AFAIK) to 25 continuations. That's why
my addin formats the code in the way you demonstrated.

(Thanks for the plug, BTW)
 
Why not build the where clause on the fly.

Dim StrWhere as String

If [Forms]![frmMaster]![chkVest50K] THEN
strWhere = " AND tblStocksGroup.HDVest50k = 'HDVest50K'
End If

If [Forms]![frmMaster]![chkHDVetst100k] THEN
StrWhere = StrWhere & " AND tblStocksGroup.HDVetst100k= 'HDVetst100k'"
End IF

If [Forms]![frmMaster]![chkETF] THEN
strWhere = StrWhere & " AND tblStocksGroup.ETF = 'ETF'"
End If

....

strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k, tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American, tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE tblStocksGroup.Group=[Forms]![frmMaster]![cboGroup] AND
tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]"

StrSQL = StrSQL & StrWhere

IF you do want to break this up you can add continuation characters like the
following

strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, " & _
"SharePrices.StockPrice, tblStocksGroup.Company, " & _
"tblStocksGroup.Group, " & _
"tblStocksGroup.Class, tblStocksGroup.HDVest50k, " & _
"tblStocksGroup.HDVetst100k, " & _
"tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue, " & _
"tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin, " & _
"tblStocksGroup.Alaska, tblStocksGroup.American, " & _ "
"tblStocksGroup.Continental, " & _
"tblStocksGroup.Delta, tblStocksGroup.Frontier, " & _
"tblStocksGroup.Southwest, " & _
"tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots, " & _
"tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong, " & _
"tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "

strSQL = strSQL & vbCrLf & "FROM SharePrices INNER JOIN tblStocksGroup " & _
" ON SharePrices.StockSymbol = tblStocksGroup.Ticker "

strSQL = strSQL & vbCrLF & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) " & _
"AND ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) " & _

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I know my SQL works. I used SQL to VBA to convert it to VBA, but now part of
the string so too long in the VBE that it causes and error. Here's the SQL:
strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k, tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American, tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND
((tblStocksGroup.HDVest50k) Like
IIf([Forms]![frmMaster]![chkVest50K]=-1,""*"",""HDVest50K"")) AND
((tblStocksGroup.HDVetst100k) Like
IIf([Forms]![frmMaster]![chkHDVetst100k]=-1,""*"",""HDVetst100k"")) AND
((tblStocksGroup.ETF) Like
IIf([Forms]![frmMaster]![chkETF]=-1,""*"",""ETF"")) AND
((tblStocksGroup.NetJets) Like
IIf([Forms]![frmMaster]![chkNetJets]=-1,""*"",""NetJets"")) AND
((tblStocksGroup.JetBlue) Like
IIf([Forms]![frmMaster]![chkJetBlue]=-1,""*"",""JetBlue"")) AND
((tblStocksGroup.JetBlueTradeLink) Like
IIf([Forms]![frmMaster]![chkJetBlueTradeLink]=-1,""*"",""JetBlueTradeLink""))
AND ((tblStocksGroup.AirWisconsin) Like
IIf([Forms]![frmMaster]![chkAirWisconsin]=-1,""*"",""AirWisconsin"")) AND
((tblStocksGroup.Alaska) Like
IIf([Forms]![frmMaster]![chkAlaska]=-1,""*"",""Alaska"")) AND
((tblStocksGroup.American) Like
IIf([Forms]![frmMaster]![chkAmerican]=-1,""*"",""American"")) AND
((tblStocksGroup.Continental) Like
IIf([Forms]![frmMaster]![chkContinental]=-1,""*"",""Continental"")) AND
((tblStocksGroup.Delta) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Frontier) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Southwest) Like
IIf([Forms]![frmMaster]![chkSouthwest]=-1,""*"",""Southwest"")) AND
((tblStocksGroup.UnitedFAS) Like
IIf([Forms]![frmMaster]![chkUnitedFAS]=-1,""*"",""UnitedFAS"")) AND
((tblStocksGroup.UnitedPilots) Like
IIf([Forms]![frmMaster]![chkUnitedPilots]=-1,""*"",""UnitedPilots"")) AND
((tblStocksGroup.WorldAirway) Like
IIf([Forms]![frmMaster]![chkWorldAirway]=-1,""*"",""WorldAirway"")) AND
((tblStocksGroup.LeveragedLong) Like
IIf([Forms]![frmMaster]![chkLeveragedLong]=-1,""*"",""LeveragedLong"")) AND
((tblStocksGroup.LeveragedShort) Like
IIf([Forms]![frmMaster]![chkLeveragedShort]=-1,""*"",""LeveragedShort"")) AND
((tblStocksGroup.Other1) Like
IIf([Forms]![frmMaster]![chkLeveragedOther1]=-1,""*"",""LeveragedOther1""))
AND ((tblStocksGroup.Other2) Like
IIf([Forms]![frmMaster]![chkLeveragedOther2]=-1,""*"",""LeveragedOther2"")))
" & vbCrLf & _
"ORDER BY SharePrices.DateTime;"

Where can I put in logical breaks to I can make this work in the VBE? I
tried this:
& vbCrLf & _
Tried it before the 'Like' and I tried before the 'AND'; nothing has worked
thus far. There must e a pretty easy way to break this up though, right.

Appreciate any help with this.

Thanks!
Ryan--
Click to expand...
 
Thanks everyone! I implemented John’s idea; works quite well!! I found this
little gold nugget a couple weeks ago:
http://allenbrowne.com/ser-71.html

I know you alluded to that Stuart. Man, that thing is nice. I’ll check out
the add-in as soon as I have a chance.
Thanks again everyone!!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


John Spencer said:
Why not build the where clause on the fly.

Dim StrWhere as String

If [Forms]![frmMaster]![chkVest50K] THEN
strWhere = " AND tblStocksGroup.HDVest50k = 'HDVest50K'
End If

If [Forms]![frmMaster]![chkHDVetst100k] THEN
StrWhere = StrWhere & " AND tblStocksGroup.HDVetst100k= 'HDVetst100k'"
End IF

If [Forms]![frmMaster]![chkETF] THEN
strWhere = StrWhere & " AND tblStocksGroup.ETF = 'ETF'"
End If

....

strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k, tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American, tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE tblStocksGroup.Group=[Forms]![frmMaster]![cboGroup] AND
tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]"

StrSQL = StrSQL & StrWhere

IF you do want to break this up you can add continuation characters like the
following

strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, " & _
"SharePrices.StockPrice, tblStocksGroup.Company, " & _
"tblStocksGroup.Group, " & _
"tblStocksGroup.Class, tblStocksGroup.HDVest50k, " & _
"tblStocksGroup.HDVetst100k, " & _
"tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue, " & _
"tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin, " & _
"tblStocksGroup.Alaska, tblStocksGroup.American, " & _ "
"tblStocksGroup.Continental, " & _
"tblStocksGroup.Delta, tblStocksGroup.Frontier, " & _
"tblStocksGroup.Southwest, " & _
"tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots, " & _
"tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong, " & _
"tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "

strSQL = strSQL & vbCrLf & "FROM SharePrices INNER JOIN tblStocksGroup " & _
" ON SharePrices.StockSymbol = tblStocksGroup.Ticker "

strSQL = strSQL & vbCrLF & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) " & _
"AND ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) " & _

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I know my SQL works. I used SQL to VBA to convert it to VBA, but now part of
the string so too long in the VBE that it causes and error. Here's the SQL:
strSql = "SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group,
tblStocksGroup.Class, tblStocksGroup.HDVest50k, tblStocksGroup.HDVetst100k,
tblStocksGroup.ETF, tblStocksGroup.NetJets, tblStocksGroup.JetBlue,
tblStocksGroup.JetBlueTradeLink, tblStocksGroup.AirWisconsin,
tblStocksGroup.Alaska, tblStocksGroup.American, tblStocksGroup.Continental,
tblStocksGroup.Delta, tblStocksGroup.Frontier, tblStocksGroup.Southwest,
tblStocksGroup.UnitedFAS, tblStocksGroup.UnitedPilots,
tblStocksGroup.WorldAirway, tblStocksGroup.LeveragedLong,
tblStocksGroup.LeveragedShort, tblStocksGroup.Other1, tblStocksGroup.Other2 "
& vbCrLf & _
"FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker " & vbCrLf & _
"WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND
((tblStocksGroup.HDVest50k) Like
IIf([Forms]![frmMaster]![chkVest50K]=-1,""*"",""HDVest50K"")) AND
((tblStocksGroup.HDVetst100k) Like
IIf([Forms]![frmMaster]![chkHDVetst100k]=-1,""*"",""HDVetst100k"")) AND
((tblStocksGroup.ETF) Like
IIf([Forms]![frmMaster]![chkETF]=-1,""*"",""ETF"")) AND
((tblStocksGroup.NetJets) Like
IIf([Forms]![frmMaster]![chkNetJets]=-1,""*"",""NetJets"")) AND
((tblStocksGroup.JetBlue) Like
IIf([Forms]![frmMaster]![chkJetBlue]=-1,""*"",""JetBlue"")) AND
((tblStocksGroup.JetBlueTradeLink) Like
IIf([Forms]![frmMaster]![chkJetBlueTradeLink]=-1,""*"",""JetBlueTradeLink""))
AND ((tblStocksGroup.AirWisconsin) Like
IIf([Forms]![frmMaster]![chkAirWisconsin]=-1,""*"",""AirWisconsin"")) AND
((tblStocksGroup.Alaska) Like
IIf([Forms]![frmMaster]![chkAlaska]=-1,""*"",""Alaska"")) AND
((tblStocksGroup.American) Like
IIf([Forms]![frmMaster]![chkAmerican]=-1,""*"",""American"")) AND
((tblStocksGroup.Continental) Like
IIf([Forms]![frmMaster]![chkContinental]=-1,""*"",""Continental"")) AND
((tblStocksGroup.Delta) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Frontier) Like
IIf([Forms]![frmMaster]![chkDelta]=-1,""*"",""Delta"")) AND
((tblStocksGroup.Southwest) Like
IIf([Forms]![frmMaster]![chkSouthwest]=-1,""*"",""Southwest"")) AND
((tblStocksGroup.UnitedFAS) Like
IIf([Forms]![frmMaster]![chkUnitedFAS]=-1,""*"",""UnitedFAS"")) AND
((tblStocksGroup.UnitedPilots) Like
IIf([Forms]![frmMaster]![chkUnitedPilots]=-1,""*"",""UnitedPilots"")) AND
((tblStocksGroup.WorldAirway) Like
IIf([Forms]![frmMaster]![chkWorldAirway]=-1,""*"",""WorldAirway"")) AND
((tblStocksGroup.LeveragedLong) Like
IIf([Forms]![frmMaster]![chkLeveragedLong]=-1,""*"",""LeveragedLong"")) AND
((tblStocksGroup.LeveragedShort) Like
IIf([Forms]![frmMaster]![chkLeveragedShort]=-1,""*"",""LeveragedShort"")) AND
((tblStocksGroup.Other1) Like
IIf([Forms]![frmMaster]![chkLeveragedOther1]=-1,""*"",""LeveragedOther1""))
AND ((tblStocksGroup.Other2) Like
IIf([Forms]![frmMaster]![chkLeveragedOther2]=-1,""*"",""LeveragedOther2"")))
" & vbCrLf & _
"ORDER BY SharePrices.DateTime;"

Where can I put in logical breaks to I can make this work in the VBE? I
tried this:
& vbCrLf & _
Tried it before the 'Like' and I tried before the 'AND'; nothing has worked
thus far. There must e a pretty easy way to break this up though, right.

Appreciate any help with this.

Thanks!
Ryan--
Click to expand...
.
Click to expand...
 
Back
Top