How to break some SQL into VBA

R

ryguy7272

I am trying to figure out how to break some SQL into VBA. I feel like I’m
almost there, but I seem to be missing something. Below is my VBA:

strSQL = "SELECT SharePrices.StockSymbol, tblStocksGroup.Company,
tblStocksGroup.Group, tblStocksGroup.Class, qry0.DateTime AS [Date],
([30Days]-[ZeroDays])/[ZeroDays] AS [30Day%Return],
([90Days]-[ZeroDays])/[ZeroDays] AS [90Day%Return],
([180Days]-[ZeroDays])/[ZeroDays] AS [180Day%Return],
([365Days]-[ZeroDays])/[ZeroDays] AS [365Day%Return], AND " & _
"tblStocksGroup.HDVest50k, tblStocksGroup.HDVest100k, 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,
qry0.StockPrice AS ZeroDays, qry30.StockPrice AS 30Days, qry90.StockPrice AS
90Days, qry180.StockPrice AS 180Days, qry365.StockPrice AS 365Days " & vbCrLf
& _
"FROM…etc.

As I know, this is for breaking within a command, like if the Select is too
long to fit on one line in the VBE:
AND " & _

As I know, this is for breaking from command to command, like Select to From:
& vbCrLf & _

I keep getting an error message that says: Run time Error
Syntax error (missing operator) in query expression ‘AND
tblStocksGroup.HDVest50k’
This is immediately after I add my break: AND " & _

I saw some documentation on where to add breaks a long time ago; can’t seem
to find it now. I know there are some places one can NOT add a break; this
must be one. Where do I add the break to make this work?
Thanks!
Ryan---
 
K

KenSheridan via AccessMonster.com

You don't need the AND when breaking a line in VBA, only if you actually want
a Boolean AND operator in the string; nor do you need to insert a carriage
return/line feed between clauses. You would normally hit the enter key
between clauses when writing a query directly in SQL for readability, but
there is no point doing so when building an SQL statement in code. So when
building a string, at the end of each physical line in the VBA editor put " &
_ and at the start of the following line put "

Forget the & vbCrLf completely.

Another way is:

strSQL = "SELECT SharePrices.StockSymbol, tblStocksGroup.Company, "
strSQL = strSQL & "tblStocksGroup.Group, tblStocksGroup.Class, "
strSQL = strSQL & "qry0.DateTime AS [Date], "
<and so on to >
strSQL = strSQL & "qry180.StockPrice AS 180Days, qry365.StockPrice AS 365Days
"
strSQL = strSQL & "FROM ......"

I believe the underscore continuation character did have a reputation for
causing corruption in early versions of Access, but I've never experienced a
problem with it.

Ken Sheridan
Stafford, England
I am trying to figure out how to break some SQL into VBA. I feel like I’m
almost there, but I seem to be missing something. Below is my VBA:

strSQL = "SELECT SharePrices.StockSymbol, tblStocksGroup.Company,
tblStocksGroup.Group, tblStocksGroup.Class, qry0.DateTime AS [Date],
([30Days]-[ZeroDays])/[ZeroDays] AS [30Day%Return],
([90Days]-[ZeroDays])/[ZeroDays] AS [90Day%Return],
([180Days]-[ZeroDays])/[ZeroDays] AS [180Day%Return],
([365Days]-[ZeroDays])/[ZeroDays] AS [365Day%Return], AND " & _
"tblStocksGroup.HDVest50k, tblStocksGroup.HDVest100k, 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,
qry0.StockPrice AS ZeroDays, qry30.StockPrice AS 30Days, qry90.StockPrice AS
90Days, qry180.StockPrice AS 180Days, qry365.StockPrice AS 365Days " & vbCrLf
& _
"FROM…etc.

As I know, this is for breaking within a command, like if the Select is too
long to fit on one line in the VBE:
AND " & _

As I know, this is for breaking from command to command, like Select to From:
& vbCrLf & _

I keep getting an error message that says: Run time Error
Syntax error (missing operator) in query expression ‘AND
tblStocksGroup.HDVest50k’
This is immediately after I add my break: AND " & _

I saw some documentation on where to add breaks a long time ago; can’t seem
to find it now. I know there are some places one can NOT add a break; this
must be one. Where do I add the break to make this work?
Thanks!
Ryan---
Click to expand...
 
A

Albert D. Kallal

What I did is toss your example into the code editor.

I then started breaking it out line by line:

strSql = "SELECT StockSymbol, Company, Group, Class, qry0.DateTime AS
[Date]," & _
"([30Days]-[ZeroDays])/[ZeroDays] AS [30Day%Return]," & _
"([90Days]-[ZeroDays])/[ZeroDays] AS [90Day%Return]," & _
"([180Days]-[ZeroDays])/[ZeroDays] AS [180Day%Return]," & _
"([365Days]-[ZeroDays])/[ZeroDays] AS [365Day%Return]," & _
" AND "

I stopped at the above, since that stray " AND " seems very out of place.

So, I not completed this for you, but my lesson is the "approach". It was
quite fast for me to encounter the " and " above.

The next best trick is to do the following right after the above code.

debug.print strSql

you can then take the string from the debug window and cut + paste it into a
new query in sql view. That way, you can quick and easy find/spot any
errors...

90Days, qry180.StockPrice AS 180Days, qry365.StockPrice AS 365Days " &
vbCrLf

The vbCrlf is not needed.

Note that some of your confusing migh be due to you trying to build a string
over many lines of code as compared to writing VBA code that allows one to
break onto the next line of code like:

msgbox "Prompt text", _
vbInformation, _
"title text

note the _ (under score). This is the line continuation character, and you
MUST break it at a comma in the command.
 
R

ryguy7272

Thanks Albert and Ken! It took me a moment to get my mind around this, but I
got it now. Thanks guys!!
Ryan---

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


KenSheridan via AccessMonster.com said:
You don't need the AND when breaking a line in VBA, only if you actually want
a Boolean AND operator in the string; nor do you need to insert a carriage
return/line feed between clauses. You would normally hit the enter key
between clauses when writing a query directly in SQL for readability, but
there is no point doing so when building an SQL statement in code. So when
building a string, at the end of each physical line in the VBA editor put " &
_ and at the start of the following line put "

Forget the & vbCrLf completely.

Another way is:

strSQL = "SELECT SharePrices.StockSymbol, tblStocksGroup.Company, "
strSQL = strSQL & "tblStocksGroup.Group, tblStocksGroup.Class, "
strSQL = strSQL & "qry0.DateTime AS [Date], "
<and so on to >
strSQL = strSQL & "qry180.StockPrice AS 180Days, qry365.StockPrice AS 365Days
"
strSQL = strSQL & "FROM ......"

I believe the underscore continuation character did have a reputation for
causing corruption in early versions of Access, but I've never experienced a
problem with it.

Ken Sheridan
Stafford, England
I am trying to figure out how to break some SQL into VBA. I feel like I’m
almost there, but I seem to be missing something. Below is my VBA:

strSQL = "SELECT SharePrices.StockSymbol, tblStocksGroup.Company,
tblStocksGroup.Group, tblStocksGroup.Class, qry0.DateTime AS [Date],
([30Days]-[ZeroDays])/[ZeroDays] AS [30Day%Return],
([90Days]-[ZeroDays])/[ZeroDays] AS [90Day%Return],
([180Days]-[ZeroDays])/[ZeroDays] AS [180Day%Return],
([365Days]-[ZeroDays])/[ZeroDays] AS [365Day%Return], AND " & _
"tblStocksGroup.HDVest50k, tblStocksGroup.HDVest100k, 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,
qry0.StockPrice AS ZeroDays, qry30.StockPrice AS 30Days, qry90.StockPrice AS
90Days, qry180.StockPrice AS 180Days, qry365.StockPrice AS 365Days " & vbCrLf
& _
"FROM…etc.

As I know, this is for breaking within a command, like if the Select is too
long to fit on one line in the VBE:
AND " & _

As I know, this is for breaking from command to command, like Select to From:
& vbCrLf & _

I keep getting an error message that says: Run time Error
Syntax error (missing operator) in query expression ‘AND
tblStocksGroup.HDVest50k’
This is immediately after I add my break: AND " & _

I saw some documentation on where to add breaks a long time ago; can’t seem
to find it now. I know there are some places one can NOT add a break; this
must be one. Where do I add the break to make this work?
Thanks!
Ryan---
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

Line-Break VBA to pass to a Query 6
how to break apart a large table field into smaller ones in SQL 4
Seeking Help With VBA and SQL Instant Query 8
How to use a combo box variable in a VBA module 1
how to pass a return value from a sql 2000 stored procedure into access vba routine 2

Top