SQL query fails in Access

irlrobins

Hey all,

I have the following SQL query that works fine if I run it in Query Analyser on the MSSQL Server.

[PHP]SELECT AddressLine1, AddressLine2, AddressLine3, Postcode
FROM SLCustomerLocation
INNER JOIN SOPOrderReturn
ON SOPOrderReturn.CustomerID = SLCustomerLocation.SLCustomerAccountID
INNER JOIN SOPDespatchReceipt
ON SOPDespatchReceipt.SOPOrderID = SOPOrderReturn.SOPOrderReturnID
AND SOPDespatchReceipt.DocumentNo = '<data>';[/PHP]

I then try to run the same query in MS Access 2007 using this code

[PHP]Dim str2SQL As String
str2SQL = "SELECT AddressLine1, AddressLine2, AddressLine3, Postcode"
str2SQL = str2SQL & " FROM SLCustomerLocation"
str2SQL = str2SQL & " INNER JOIN SOPOrderReturn"
str2SQL = str2SQL & " ON SOPOrderReturn.CustomerID = SLCustomerLocation.SLCustomerAccountID"
str2SQL = str2SQL & " INNER JOIN SOPDespatchReceipt"
str2SQL = str2SQL & " ON SOPDespatchReceipt.SOPOrderID = SOPOrderReturn.SOPOrderReturnID"
str2SQL = str2SQL & " AND SOPDespatchReceipt.DocumentNo = "
str2SQL = str2SQL & "'" & DispatchNumber & "';"

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset(str2SQL)[/PHP]

But I get a Syntax Error, Missing operator Error code 3075 when I try to run this.

Now I have a feeling that I need to use brackets, but I'm not sure how and where they need to be? Anyone got any ideas?

Thanks

Ginger

Change your AND to a WHERE in the last section and I think you should be good

irlrobins

Cheers, I'll give that a go later this week. I got a around it using a couple of select sub queries. Not pretty, but it let me continue on with the rest of it for a bit.