tllewellyn tllewellyn - 2 months ago 19
SQL Question

MS Access Left Join Not Supported - Combobox as Variable

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


I am receiving a LEFT JOIN not supported error. I have tried adding brackets and it goes through error free but I see everything in the table instead of just those by the specific aircraft number. I have tested the statement by hardcoding in the aircraftID I want (eg: 428) but I still get the join not supported error with the "AND t2.AircraftID = 428" highlighted.

Am I using the wrong join here? What is the correct way of writing this query?

Answer

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 ;
Comments