×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access ODBC Driver Reporting Syntax Error in Valid SQL Query

Access ODBC Driver Reporting Syntax Error in Valid SQL Query

Access ODBC Driver Reporting Syntax Error in Valid SQL Query

(OP)
Hi,

Here is the query I am trying to execute. As far as I can tell it is a perfectly valid SQL query:

SELECT T1.PaymentNumber, T1.TillID, T1.BranchID, T1.TransactionNumber, T1.Payment, T1.CreationDate, T2.SalesNumber, T2.CustomerID, T2.PLU, T2.Description, T2.Retail, T3.FirstName, T3.LastName, T3.Street, T3.District, T3.Town, T3.County, T3.Postcode FROM tblPayment AS T1, tblStockOut AS T2, tblCustomer AS T3 INNER JOIN T2 ON T1.TillID = T2.TillID INNER JOIN T2 ON T1.BranchID = T2.BranchID INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber INNER JOIN T2 ON T3.CustomerID = T2.CustomerID WHERE T2.TillID = 'A12' AND T2.BranchID = 'WW' AND T2.TransactionNumber = 9181 AND T2.CustomerID = 35654

This causes the Access ODBC Driver to report:

PHP Warning:  odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'T1.TillID = T2.TillID INNER JOIN T2 ON T1.BranchID = T2.BranchID INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber INNER JOIN T2 ON T3.CustomerID = T2.CustomerID'., SQL state 37000 in SQLExecDirect in C:\test.php on line 26

Here's what I'm trying to do:

I have three tables: tblPayment, tblStockOut & tblCustomer. Three fields are common to both tblPayment and tblStockOut - TillID, BranchID & TransactionNumber. These three fields are indexed but are not primary keys, they all have duplicate entries. However, there can only be one TillID, BranchID and TransactionNumber that match, i.e. only one record in each table will have a TillID of A12, a BranchID of WW and a TransactionNumber of 9181. I'm also using the CustomerID from the record found in tblStockOut to fetch the customers' details from tblCustomer. tblPayment and tblStockOut are not related to each other inside Access (actually none of the tables in the database are).

I cannot make any changes to the database, it is part of a third-party off-the-shelf application and uses the Access 97 runtime.

RE: Access ODBC Driver Reporting Syntax Error in Valid SQL Query

(OP)
I think I've some progress but still stuck. I have remembered Access requires parentheses when doing multiple joins, so I have changed the SQL to the following:

SELECT T1.PaymentNumber, T1.TillID, T1.BranchID, T1.TransactionNumber, T1.Payment, T1.CreationDate, T2.SalesNumber, T2.CustomerID, T2.PLU, T2.Description,  T2.Retail, T3.FirstName, T3.LastName, T3.Street, T3.District, T3.Town, T3.County, T3.Postcode FROM ((((((tblPayment AS T1,) tblStockOut AS T2,) tblCustomer AS T3) INNER JOIN T2 ON T1.TillID = T2.TillID) INNER JOIN T2 ON T1.BranchID = T2.BranchID) INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber) INNER JOIN T2 ON T3.CustomerID = T2.CustomerID WHERE T2.TillID = 'A12' AND T2.BranchID = 'WW' AND T2.TransactionNumber = 9181 AND T2.CustomerID = 35654

Now I'm getting:

PHP Warning:  odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation., SQL state 37000 in SQLExecDirect in C:\test.php on line 26

So I'm still stuck.

RE: Access ODBC Driver Reporting Syntax Error in Valid SQL Query

(OP)
I spotted an error in the previous statement and corrected it to the following, but I still get the same error:

SELECT T1.PaymentNumber, T1.TillID, T1.BranchID, T1.TransactionNumber, T1.Payment, T1.CreationDate, T2.SalesNumber, T2.CustomerID, T2.PLU, T2.Description,  T2.Retail, T3.FirstName, T3.LastName, T3.Street, T3.District, T3.Town, T3.County, T3.Postcode FROM (((((tblPayment AS T1, tblStockOut AS T2, tblCustomer AS T3) INNER JOIN T2 ON T1.TillID = T2.TillID) INNER JOIN T2 ON T1.BranchID = T2.BranchID) INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber) INNER JOIN T2 ON T3.CustomerID = T2.CustomerID) WHERE T2.TillID = 'A12' AND T2.BranchID = 'WW' AND T2.TransactionNumber = 9181 AND T2.CustomerID = 35654

RE: Access ODBC Driver Reporting Syntax Error in Valid SQL Query

(OP)
OK made some (small) progress but still getting stuck. I now have this:

SELECT T1.PaymentNumber, T1.TillID, T1.BranchID, T1.TransactionNumber, T1.Payment, T1.CreationDate, T2.SalesNumber, T2.CustomerID, T2.PLU, T2.Description, T2.Retail, T3.FirstName, T3.LastName, T3.Street, T3.District, T3.Town, T3.County, T3.Postcode FROM (((((tblPayment AS T1) INNER JOIN tblStockOut AS T2 ON T1.TillID = T2.TillID) INNER JOIN T2 ON T1.BranchID = T2.BranchID) INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber) INNER JOIN tblCustomer AS T3 ON T3.CustomerID = T2.CustomerID) WHERE T2.TillID = 'A12' AND T2.BranchID = 'WW' AND T2.TransactionNumber = 9181 AND T2.CustomerID = 35654

But I'm getting: PHP Warning:  odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Join expression not supported., SQL state S1000 in SQLExecDirect in C:\test.php
on line 26

RE: Access ODBC Driver Reporting Syntax Error in Valid SQL Query





Inner joins can also be coded more simply, without all the set of parentheses...

CODE

SELECT
  T1.PaymentNumber
, T1.TillID
, T1.BranchID
, T1.TransactionNumber
, T1.Payment
, T1.CreationDate
, T2.SalesNumber
, T2.CustomerID
, T2.PLU
, T2.Description
, T2.Retail
, T3.FirstName
, T3.LastName
, T3.Street
, T3.District
, T3.Town
, T3.County
, T3.Postcode

FROM
  tblPayment  AS T1
, tblStockOut AS T2
, tblCustomer AS T3

Where T1.TillID            = T2.TillID
  AND T1.BranchID          = T2.BranchID
  AND T1.TransactionNumber = T2.TransactionNumber
  AND T3.CustomerID        = T2.CustomerID
  AND T2.TillID            = 'A12'
  AND T2.BranchID          = 'WW'
  AND T2.TransactionNumber = 9181
  AND T2.CustomerID        = 35654
Are you sure that the TransactionNumber & CustomerID are numeric data types?

Skip,

glassesJust traded in my old subtlety...
for a brand NUANCE!tongue

RE: Access ODBC Driver Reporting Syntax Error in Valid SQL Query

(OP)
It looks like the simple answers are always the best. Thanks, SkipVought - that did the trick!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close