Syntax Error in SQL Query-Ms Access

Question

I have the following SQl query in VBA and it gives me a syntax error when I run the form. I tried writing the query again, but I ended up with the same error. Any help would be appreciated.Thank you:

 Set RS = CurrentDb.OpenRecordset("SELECT & _
          Purchases.SupplierLocation & _
        , Purchases.MinWeight & _
        , Purchases.MaxWeight & _
        , Purchases.PortofDischarge & _
        , Purchases.PlaceofDelivery & _
        , Purchases.PriceTermsLocation " & _

         "FROM ( & _
          Vendors & _
          RIGHT JOIN Purchases & _
             ON Vendors.VendorID=Purchases.VendorID & _
          ) & _
          INNER JOIN & _
              (SELECT Max(PODate) as MaxPODate & _
               FROM Purchases & _
               WHERE Purchases.VendorID = '" & Nz(Me.VendorID, "") & "' & _
               AND Purchases.Grade = '" & Nz(Me.Grade, "") & "' & _
               AND Purchases.PurchaseID <> " & Nz(Me.PurchaseID, 0) & " & _
              ) RecentPurchase & _
             ON Purchases.PODate = RecentPurchase.MaxPODate " & _
         "WHERE Purchases.VendorID = '" & Nz(Me.VendorID, "") & "' & _
          AND Purchases.Grade = '" & Nz(Me.Grade, "") & "' & _
          AND Purchases.PurchaseID <> '" & Nz(Me.PurchaseID, 0)"' & _
         "ORDER BY Min(IIf(Vendors.VendorID=Purchases.VendorID,1,2))")

I hope this isn't too rude, but you would probably be able to easily troubleshoot your code if it was well-formatted... it certainly couldn't hurt at least. — Jacob H
– Jacob H, Commented May 9, 2017 at 18:26

Simon · Accepted Answer · 2017-05-09 18:32:32Z

1

As @JacobH stated it could be clearer but it could be that just before the order by :

"' & _ "ORDER BY

should be

"'" & _ "ORDER BY

answered May 9, 2017 at 18:32

Simon

7844 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jacob H Over a year ago

Good spotting, you have better eyes than I do!

user7631583 Over a year ago

Tried it. It is still giving me the same syntax error.

PQ Co · Accepted Answer · 2017-05-09 18:57:17Z

you missed a lot of double quotes:

Set RS = CurrentDb.OpenRecordset("SELECT " & _
              "Purchases.SupplierLocation " & _
            ", Purchases.MinWeight " & _
            ", Purchases.MaxWeight " & _
            ", Purchases.PortofDischarge " & _
            ", Purchases.PlaceofDelivery " & _
            ", Purchases.PriceTermsLocation " & _
             "FROM ( " & _
              "Vendors " & _
              "RIGHT JOIN Purchases " & _
               "  ON Vendors.VendorID=Purchases.VendorID " & _
              ") " & _
              "INNER JOIN " & _
                  "(SELECT Max(PODate) as MaxPODate " & _
                  " FROM Purchases " & _
                  " WHERE Purchases.VendorID = '" & Nz(Me.VendorID, "") & "' " & _
                  " AND Purchases.Grade = '" & Nz(Me.Grade, "") & "' " & _
                  " AND Purchases.PurchaseID <> " & Nz(Me.PurchaseID, 0) & _
                 " ) RecentPurchase " & _
                " ON Purchases.PODate = RecentPurchase.MaxPODate " & _
             "WHERE Purchases.VendorID = '" & Nz(Me.VendorID, "") & "' " & _
             " AND Purchases.Grade = '" & Nz(Me.Grade, "") & "' " & _
             " AND Purchases.PurchaseID <> '" & Nz(Me.PurchaseID, 0) & "' " & _
             "ORDER BY Min(IIf(Vendors.VendorID=Purchases.VendorID,1,2))")

Collectives™ on Stack Overflow

Syntax Error in SQL Query-Ms Access

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related