Query does not work in MS Access in CF

[Botman63]

I have this query that works fine in MS Access itself, but when I put it into
my CFM page it does not work. The query is this:

SELECT Products.Short_Description, Sizes.Size, Stock.StockCode,
Products.Color_Code, Products.Piece, Stock.StitchCount
FROM (Sizes INNER JOIN Products ON Sizes.Size_ID = Products.Size_ID) INNER
JOIN (Stock INNER JOIN TempCart ON Stock.StockCode = TempCart.StockCode) ON
Products.SKU = TempCart.SKU
WHERE (((TempCart.CFID)=#Session.CFID#) AND
((TempCart.CFTOKEN)=#Session.CFToken#))

The only thing that Access does is place double-quotes around the session
variables. Otherwise this query is exactly the way it is in Access. I tried it
with single quotes and no quotes (since these session variables are only
numeric variables, they should not have quotes around them anyway). Can someone
tell me what the problem is?

Thanks,

Bruce

[paross1]

Does it just not return what you expect ,or is it actually giving you an error message and, if so, what error are you seeing?

Phil

[Botman63]

Error:
Error Executing Database Query.

The error occurred in C:\Inetpub\wwwroot\Stitches\ShoppingCart.cfm: line 28

26 : SELECT Products.Short_Description, Sizes.Size, Stock.StockCode,
Products.Color_Code, Products.Piece, Stock.StitchCount
27 : FROM (Sizes INNER JOIN Products ON Sizes.Size_ID = Products.Size_ID)
INNER JOIN (Stock INNER JOIN TempCart ON Stock.StockCode = TempCart.StockCode)
ON Products.SKU = TempCart.SKU
28 : WHERE (((TempCart.CFID)=#Session.CFID#) AND
((TempCart.CFTOKEN)=#Session.CFToken#))
29 : </cfquery>

[paross1]

What happens if you replace #Session.CFID# and #Session.CFToken# with constants 202 and 75117395 respectively within your cfquery?

Phil

[James@nps]

New to coding but I would try this:

WHERE (((TempCart.CFID)='#Session.CFID#') AND ((TempCart.CFTOKEN)='#Session.CFToken#'))

[paross1]

He already stated in the original post that I tried it with single quotes and no quotes...

Phil

[JMGibson3]

I would suspect Size is one of those keywords that cannot pass throught the
ODBC interface (although Native Access still allows it). Either rename Size in
the database or try surrounding with brackets Sizes..

[Botman63]

It was Size. Although it works fine in Access without brackets, I had to put brackets around it in CF. Thanks JM.