Can anyone tell me how to get this statement to work with access? I use 
this statement syntax all the time with SQL Server, but access throws a 
fit when I feed it this.

strSQL = "SELECT p.productID, p.name, ptb.duration, ptb.durationperiod " &_
"FROM products p " &_
"INNER JOIN producttypes pt ON p.producttype = pt.producttype " &_
"LEFT OUTER JOIN producttimebased ptb ON ptb.productID = ptb.productID " &_
"WHERE pt.subscription = true " &_
"ORDER BY p.producttype, p.productID"

Thanks,
Jules.

> Can anyone tell me how to get this statement to work with access? I use 
t> his statement syntax all the time with SQL Server, but access throws a 
f> it when I feed it this.

> strSQL = "SELECT p.productID, p.name, ptb.duration, ptb.durationperiod " 
&_
"> FROM products p " &_
"> INNER JOIN producttypes pt ON p.producttype = pt.producttype " &_
"> LEFT OUTER JOIN producttimebased ptb ON ptb.productID = ptb.productID " 
&_
"> WHERE pt.subscription = true " &_
"> ORDER BY p.producttype, p.productID"

> Thanks,
J> ules.

Couple of points:

1) The line with LEFT OUTER JOIN has "ptb.ProductID = ptb.productID"??
   I assume it should be  "pt.productID = ptb.productID"

2) This might be red herring but as far as I know the JOIN syntax in 
ACCESS SQL (i.e. in a standalone ACCESS app)  is different to standard 
SQL. I have not used ASP with ACCESS but the same query inside an ACCESS 
app would be something like:


strSQL = "SELECT p.productID, p.name, ptb.duration, ptb.durationperiod " &_
         "FROM products p " &_
         "INNER JOIN (producttimebased ptb  LEFT OUTER JOIN producttype pt 
ON ptb.productID = pt.productID) " &_
         "ON p.producttype = pt.producttype " &_
         "WHERE pt.subscription = true " &_
         "ORDER BY p.producttype, p.productID"

You asked this question on two different lists (including this one) on the
26th of May, and I answered it on both lists.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <julian@f...>
To: "Access ASP" <access_asp@p...>
Sent: Saturday, June 15, 2002 1:15 AM
Subject: [access_asp] SQL Statement trouble with MS Access


: Can anyone tell me how to get this statement to work with access? I use
: this statement syntax all the time with SQL Server, but access throws a
: fit when I feed it this.
:
: strSQL = "SELECT p.productID, p.name, ptb.duration, ptb.durationperiod "
&_
: "FROM products p " &_
: "INNER JOIN producttypes pt ON p.producttype = pt.producttype " &_
: "LEFT OUTER JOIN producttimebased ptb ON ptb.productID = ptb.productID "
&_
: "WHERE pt.subscription = true " &_
: "ORDER BY p.producttype, p.productID"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~