SQL string error in excel vba

[krice1974]

Hi. I'm trying to execute a SQL command to pull an access query into excel. The select query works perfectly in access, and I've pasted the SQL into the procedure below. When running it I'm getting "syntax error in FROM clause." Any ideas? Thanks in advance... Kevin

'Set the SQL string.
sSQL = "SELECT tblStocksPricingVol.Symbol, tblStocksPricingVol.PricingVolDate, tblStocksPricingVol.Volume, " & _
    "tblStocksPricingVol.HighPrice, tblStocksPricingVol.ClosePrice, [HighPrice]-[ClosePrice] AS " & _
    "DiffBetwClAndHigh, FormatPercent([DiffBetwClAndHigh]/[HighPrice]) AS PctClFromHigh FROM " & _
    "tblStocksPricingVol WHERE (((tblStocksPricingVol.PricingVolDate)=(SELECT Max(T2.PricingVolDate)" & _
    "FROM tblStocksPricingVol AS T2 WHERE T2.Symbol = tblStocksPricingVol.Symbol)) AND " & _
    "((tblStocksPricingVol.Volume)>=850000));"

 

[james_lankford]

I'm assuming FormatPercent is a custom access function in an access module ?
try taking it out

 

[krice1974]

It's included in their library, so it's not custom, but I'll try it anyway...

 

[krice1974]

No such luck unfortunately.

 

[tinkythomas]

You need a space at the end of this line...

"tblStocksPricingVol WHERE (((tblStocksPricingVol.PricingVolDate)=(SELECT Max(T2.PricingVolDate)" & _

should look like this...

"tblStocksPricingVol WHERE (((tblStocksPricingVol.PricingVolDate)=(SELECT Max(T2.PricingVolDate) " & _

Hope that helps.

 

[krice1974]

I tried that before. That didn't fix it either. Thanks though! *scratches head*

 

[james_lankford]

try replacing your code with this

'Set the SQL string.
sSQL = ""
sSQL = sSQL & "SELECT " & vbCrLf
sSQL = sSQL & "    t1.Symbol, " & vbCrLf 
sSQL = sSQL & "    t1.PricingVolDate, " & vbCrLf 
sSQL = sSQL & "    t1.Volume, " & vbCrLf
sSQL = sSQL & "    t1.HighPrice, " & vbCrLf 
sSQL = sSQL & "    t1.ClosePrice, " & vbCrLf 
sSQL = sSQL & "    t1.[HighPrice] - t1.[ClosePrice] AS DiffBetwClAndHigh, " & vbCrLf
sSQL = sSQL & "    (t1.[HighPrice] - t1.[ClosePrice]) / t1.[HighPrice] AS PctClFromHigh " & vbCrLf
sSQL = sSQL & "FROM " & vbCrLf
sSQL = sSQL & "    tblStocksPricingVol as t1 " & vbCrLf
sSQL = sSQL & "WHERE " & vbCrLf
sSQL = sSQL & "( " & vbCrLf
sSQL = sSQL & "    ( " & vbCrLf
sSQL = sSQL & "        t1.PricingVolDate = " & vbCrLf
sSQL = sSQL & "        ( " & vbCrLf
sSQL = sSQL & "            SELECT " & vbCrLf
sSQL = sSQL & "                Max(T2.PricingVolDate)" & vbCrLf
sSQL = sSQL & "            FROM " & vbCrLf
sSQL = sSQL & "                tblStocksPricingVol AS T2 " & vbCrLf
sSQL = sSQL & "             WHERE " & vbCrLf
sSQL = sSQL & "                 T2.Symbol = t1.Symbol " & vbCrLf
sSQL = sSQL & "        ) " & vbCrLf
sSQL = sSQL & "    ) " & vbCrLf 
sSQL = sSQL & "    AND " & vbCrLf 
sSQL = sSQL & "    ( " & vbCrLf
sSQL = sSQL & "        t1.Volume >= 850000 " & vbCrLf
sSQL = sSQL & "    ) " & vbCrLf
sSQL = sSQL & ") " & vbCrLf

debug.print sSQL

msgbox sSQL

 

[krice1974]

Same error. I very much appreciate that effort though... But, "syntax error in FROM clause" still.

 

[james_lankford]

so let's try this

sSQL = sSQL & "        t1.PricingVolDate = #01/01/2009# " & vbCrLf

and take out the inner sql just to see if it works
</pre>

 

[SydneyGeek]

Try amending your original from this:

"FROM tblStocksPricingVol AS T2 WHERE T2.Symbol = tblStocksPricingVol.Symbol)) AND " & _

to this:

"FROM tblStocksPricingVol AS T2 WHERE T2.Symbol = '" & tblStocksPricingVol.Symbol & "')) AND " & _

Denis