If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to break some SQL into VBA
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
|
|||
|
|||
How to break some SQL into VBA
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 ryguy7272 wrote: 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--- -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#3
|
|||
|
|||
How to break some SQL into VBA
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. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#4
|
|||
|
|||
How to break some SQL into VBA
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" wrote: 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 ryguy7272 wrote: 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--- -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
Thread Tools | |
Display Modes | |
|
|