JOIN expression not supported

[Mr_P]

Hi

I am using vb.Net to connect to an access db. In access 2010 this query works fine, but in vb.Net, it errors with 'JOIN expression not supported'. Now I am a new user to access and vb.Net so could do with some help to straighten this query out and show me my error. Many thanks

sql = "SELECT Boxes.Box, Boxes.CustRef, Boxes.Customer " &
                  "FROM (Requests INNER JOIN [Request Boxes] ON Requests.[Request no] = [Request Boxes].[Request no]) INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box" &
                  "WHERE (Requests.[Request no]) = '" & item & "' " &
                  "AND ((Boxes.Customer) = '" & customer2 & "'))"

 

[JHB]

I don't know if it is this, but you are missing a space before Where

[Request Boxes].Box = Boxes.Box" &
"WHERE (Requests.[Request no]) = '" & item & "' " &

And then there is a ")" to much

 "AND ((Boxes.Customer) = '" & customer2 & "')[COLOR=Red][B])[/B][/COLOR]"

 

[Mr_P]

Thanks very much JHB.

 

[namliam]

not sure about vb.net but in vba you need to add _ behind each line, but I am sure you wouldnt make that mistake...
On a whole though, really my advice to keep your code readable overall..... something along these lines

sql = " SELECT Boxes.Box, Boxes.CustRef, Boxes.Customer " &
      " FROM      ( Requests " & 
      " INNER JOIN [Request Boxes] ON Requests.[Request no] = [Request Boxes].[Request no]) " & 
      " INNER JOIN Boxes           ON [Request Boxes].Box = Boxes.Box" &
      " WHERE (Requests.[Request no]) = '" & item & "' " &
      " AND   ((Boxes.Customer) = '" & customer2 & "'))"

You will agree makes a lot of it MUCH more readable, thus maintainable and you would likely have spotted the extra ) as well.