can't determine the syntax error

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi All

Am hoping someone can point me in the right direction with the
following sql statement (in VB code). When it is run, I get a syntax
error (missing operator) and it points to just after the 'where'
operator.
var's addr & binnum show up correctly in the error message. All help
greatly appreciated as have tried everything I can think of. TIA
Richard



strSQL = "SELECT Customer.[Company Name], ADDRESS.ADDRESS, Box.BoxSize,
Material.Weight, Material.[Dump Location], [(P/U)Date].[P/U Date],
[(P/U)Date].[Truck #(P/U)], [(P/U)Date].[Driver Name] " & _
"FROM (Day_MAIN INNER JOIN (Customer INNER JOIN (ADDRESS INNER JOIN
(Box INNER JOIN Payment ON Box.ID_Box = Payment.ID_Box_F) ON
ADDRESS.ID_Company = Box.ID_Custom_F) ON Customer.Custom_ID =
ADDRESS.ID_CUST_F) ON Day_MAIN.ID_day = Customer.ID_Day_F) INNER JOIN
(Material INNER JOIN (Del_truck INNER JOIN [(P/U)Date] ON
Del_truck.ID_Del = [(P/U)Date].ID_Del_F) ON Material.ID_Material =
Del_truck.ID_Mat_F) ON Payment.ID_Payment = Material.ID_Paym_F " & _
"WHERE (((ADDRESS.ADDRESS)= " & addr & " ) AND ((Box.BoxSize)= " &
binnum & ") AND (([(P/U)Date].[P/U Date]) Is Null));"
 
When SQL doesn't work properly, troubleshoot by simplfying the statement
until it runs then slowly buiding it back up while testing every step of the
way.

Does it run with the Where clause removed?
No - problem is above that.
Yes - Add the P/U part of the Where clause.
Once you get that running, add the addr or binnum.
Once you get that running, add the other part.

If you can't get it to run, make it a normal saved query with addr and
binnum as parameters or hard coded in. See if it runs then.
 
Back
Top