SQL syntax error with vba

[Accessor]

Hi all. I'm getting a "syntax error in FROM clause" when I try to run this
query through vba and ado within excel. It works fine as a query in the
database. I've checked for proper formatting. All this is is my sql copied
from the working query, converted to a string for ADO. Any help? I'm at my
wits' end... 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));"

 

[Sylvain Lafontaine]

When you have an error, the first thing to do is often to go with
elimination: you retrieve parts until the query no longer return a syntax
error. In this case, FormatPercent() looks suspicious to me because I think
it's an Access VBA function and not a JET sql query function.

The use of [] as delimiters could also be a problem with here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Accessor]

Hi Sylvain, thanks for the reply. I checked that by running it without the
format percent function and still, same error. *scratches head*

When you have an error, the first thing to do is often to go with
elimination: you retrieve parts until the query no longer return a syntax
error. In this case, FormatPercent() looks suspicious to me because I think
it's an Access VBA function and not a JET sql query function.

The use of [] as delimiters could also be a problem with here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi all. I'm getting a "syntax error in FROM clause" when I try to run this
query through vba and ado within excel. It works fine as a query in the
database. I've checked for proper formatting. All this is is my sql copied
from the working query, converted to a string for ADO. Any help? I'm at my
wits' end... 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));"

 

[Sylvain Lafontaine]

Your query looks fine to me. Maybe a missing space between the ) and the
last FROM? (Shouldn't be a problem but we never know).

It could also be the subquery: in many cases, Access seems to like the use
of [ ]. for enclosing a subquery instead of using parenthesis. It also
doesn't like unnamed subqueries but this shouldn't be a problem here. You
could also give an alias to the first tblStocksPricingVol instead of just
the second one.

Try removing the delimiters []. If this doesn't work, try removing other
things until it works.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi Sylvain, thanks for the reply. I checked that by running it without the
format percent function and still, same error. *scratches head*

When you have an error, the first thing to do is often to go with
elimination: you retrieve parts until the query no longer return a syntax
error. In this case, FormatPercent() looks suspicious to me because I
think
it's an Access VBA function and not a JET sql query function.

The use of [] as delimiters could also be a problem with here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi all. I'm getting a "syntax error in FROM clause" when I try to run
this
query through vba and ado within excel. It works fine as a query in the
database. I've checked for proper formatting. All this is is my sql
copied
from the working query, converted to a string for ADO. Any help? I'm at
my
wits' end... 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));"