I have 3 tables used for tracking deviations to aircraft maintenance.
1 - Deviations - describes the deviation in place.
2 - Aircraft - A list of aircraft in the fleet
3 - Aircraft Deviations - columns with a unique ID, DeviationID, and AircraftID
I am trying to open a report, showing all the deviations against a specific aircraftID, using a combobox on a form to select the aircraft by ID. I am using the combobox's onUpdate event to modify a query definition with the following SQL:
strSQL = "SELECT Deviation, Rev, Title, Issued, ExpiryDate, ExpiryHours, " _
& "ExpOther, Active FROM Deviations t1 LEFT JOIN " _
& "AircraftDeviations t2 ON t1.ID = t2.DeviationID " _
& "AND t2.AircraftID = " & Me!cmbAircraft
The problem is that MS Access does not allow constants in the
ON clause. Alas. This is just one of many awkward limitations in the Access version of SQL.
You can do what you want with a subquery, though:
SELECT Deviation, Rev, Title, Issued, ExpiryDate, ExpiryHours, ExpOther, Active FROM Deviations as d LEFT JOIN (SELECT ad.* FROM AircraftDeviations as ad WHERE ad.AircraftID = Me!cmbAircraft ) as ad ON d.ID = ad.DeviationID ;