SQL Statement

[Gary.Lowe]

Hi All

I have an SQL String which works in SQL Server but not in access

The SQL is as follows:

SELECT TTrnHdrOpenedBy, TTrnHdrTimeStamp, PayNo, PayDescription, TrnPayAmount, TrnMemMember, TTrnDetType
FROM (tblTillTransactionHeader INNER JOIN tblTransactionPayments
ON tblTillTransactionHeader.TTrnHdrTranNo = tblTransactionPayments.TrnPayBillNo)
INNER JOIN tblPaymentMethods ON tblPaymentMethods.PayNo = tblTransactionPayments.TrnPayPayMethod
INNER JOIN tblTransactionMembers ON tblTillTransactionHeader.TTrnHdrTranNo = tblTransactionMembers.TrnMemHdrNo
INNER JOIN tblTillTransactionDetail ON tblTillTransactionHeader.TTrnHdrTranNo = tblTillTransactionDetail.TTrnDetHeaderNo
WHERE TTrnHdrTranNo ='CRX1'


Can anybody help me suss this out.
The error it returns in Access is Syntax Error (Missing Operator) in query expression

tblPaymentMethods.PayNo = tblTransactionPayments.TrnPayPayMethod
INNER JOIN tblTransactionMembers
ON tblTillTransactionHeader.TTrnHdrTranNo = tblTransactionMembers.TrnMemHdrNo
INNER JOIN tblTillTransactionDetail
ON tblTillTransactionHeader.TTrnHdrTranNo = tblTillTransactionDetail.TTrnDetHeaderNo
WHERE TTrnHdrTranNo ='CRX1'

Thanks

[evaleah]

Are all these tables in Access? If so, I would recommend using the Access query builder to do most of the SQL for you. Just go into design view, show all the tables you want to select from and then choose the fields you want. Then switch to the SQL view and add the "where field = 'CRX1'" at the end of the statement. This way the statment will be built exactly how Access likes it.

Eva