none
Join statement will not compile, displays in red RRS feed

  • Question

  • Can someone let me know why this statement will not work in MS Access?

    Set rst1 = db.OpenRecordset("SELECT tblDlrVehicles.* FROM tblDlrVehicles " & _
               " INNER JOIN tblDlrMain ON tblDlrVehicles.DlrName = tblDlrMain.DlrName " & _
               " WHERE tblDlrVehicles.Model = '" & rst!Model "  & _
               " AND tblDlrVehicles.MakeCar = '" & rst!MakeCar " AND tblDlrMain.DlrActive = True " & _
               " ORDER BY tblDlrVehicles.DlrName, tblDlrVehicles.MakeCar, tblDlrVehicles.YearCar, " & _
               " tblDlrVehicles.Model, tblDlrVehicles.Count;", dbOpenDynaset)

      It displays in red and I cannot get the code correct.  Thanx in advance.

    Thursday, June 20, 2019 9:45 AM

All replies

  • Try

    Set rst1 = db.OpenRecordset("SELECT tblDlrVehicles.* FROM tblDlrVehicles" & _
               " INNER JOIN tblDlrMain ON tblDlrVehicles.DlrName = tblDlrMain.DlrName" & _
               " WHERE tblDlrVehicles.Model = '" & rst!Model & "'" & _
               " AND tblDlrVehicles.MakeCar = '" & rst!MakeCar & "' AND tblDlrMain.DlrActive = True" & _
               " ORDER BY tblDlrVehicles.DlrName, tblDlrVehicles.MakeCar, tblDlrVehicles.YearCar," & _
               " tblDlrVehicles.Model, tblDlrVehicles.Count;", dbOpenDynaset)


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, June 20, 2019 10:18 AM