How to break some SQL into VBA
in [Queries]
|
From: ryguy7272 on 28 Feb 2010 18:35 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''.
From: KenSheridan via AccessMonster.com on 28 Feb 2010 19:18 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/Forums.aspx/access-queries/201002/1
From: Albert D. Kallal on 28 Feb 2010 19:29 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 pleaseNOOSpamKallal(a)msn.com
From: ryguy7272 on 28 Feb 2010 22:39 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/Forums.aspx/access-queries/201002/1 > > . >
|
Pages: 1 Prev: Why does Access 2007 save query changes w/o prompting? Next: Add percentages |