I am trying to write a SQL query in Access 2007 to pull data from linked tables from an outside data source, based on an end users selection in a field on a form. This data is then used to populate other fields on the same form.
I've used this design in several databases I wrote in Access 2010 without any issues. However, in this 2007 database, I am getting a "Run-Time error 3075 (Missing Operator)" failing on the identified line in the code.
Can someone verify whether this is unsupported syntax in v2007, or can you point out the problem I am not seeing? Thank you!
strSQL = "SELECT ms.MST_SHIP_NUM, o.ORD_NUM, ms.SHIP_DT, ad.NAME, ms.CAR_ID, ms.EST_CUBE, vss.VSL_ID, vss.SEAL_NUM " & _
"FROM CPCMEP.MST_SHIP ms " & _
"LEFT JOIN CPCMEP.ORD_ACT oa ON ms.MST_SHIP_NUM = oa.MST_SHIP_NUM " & _
"LEFT JOIN CPCMEP.ORD o ON oa.ORD_NUM = o.ORD_NUM " & _
"LEFT JOIN CPCMEP.CUS cu ON o.CUS_ID = cu.CUS_ID " & _
"LEFT JOIN CPCMEP.ADDR ad ON cu.MARK_FOR_ADDR_ID = ad.ADDR_ID " & _
"LEFT JOIN CPCMEP.VSL vs ON ms.MST_SHIP_NUM = vs.MST_SHIP_NUM " & _
"LEFT JOIN CPCMEP.VSL_SEAL vss ON ms.MST_SHIP_NUM = vss.MST_SHIP_NUM " & _
"WHERE ((ms.MST_SHIP_NUM) Like " & Forms![frm_Outbound_Load_Integrity]![MASTER_SHIP_NUM] & ");"
Set rst = CurrentDb.OpenRecordset(strSQL) 'This is the line that is failing
' If there are records for the MS#...
If rst.RecordCount > 0 Then
Me.CARRIER = rst!ms.CAR_ID
Me.SEAL_NUM = rst!vss.SEAL_NUM
Me.CUSTOMER_NAME = rst!ad.Name
Me.SCHED_SHIP_TIME = TimeValue(rst!ms.SHIP_DT)
Me.SHIPMENT_TOTAL_CUBE = rst!ms.EST_CUBE