ACC200: error in SQL string

  • Thread starter Thread starter A Man
  • Start date Start date
A

A Man

I have a really long SQL string I'm trying to assing to a string
variable in VBA code. Here it is:

sSQL = "SELECT Mid(Str([billed_dt]),2,4) AS [Year],
dbo_ARCUSFIL_SQL.slspsn_no, _
dbo_ARCUSFIL_SQL.cus_no, dbo_OELINHST_SQL.item_desc_1,
dbo_OELINHST_SQL.item_no, _
dbo_OELINHST_SQL.qty_ordered, dbo_OELINHST_SQL.unit_price,
dbo_ARCUSFIL_SQL.cus_name, _
dbo_ARCUSFIL_SQL.addr_1, dbo_ARCUSFIL_SQL.addr_2, dbo_ARCUSFIL_SQL.city,
_
dbo_ARCUSFIL_SQL.state, dbo_ARCUSFIL_SQL.zip, dbo_ARCUSFIL_SQL.country _
FROM dbo_ARCUSFIL_SQL _
LEFT JOIN dbo_OELINHST_SQL ON dbo_ARCUSFIL_SQL.cus_no =
dbo_OELINHST_SQL.cus_no _
WHERE (((Mid(Str([billed_dt]),2,4))=2007) AND _
((dbo_ARCUSFIL_SQL.slspsn_no)='30' Or (dbo_ARCUSFIL_SQL.slspsn_no)='31')
AND _
((dbo_OELINHST_SQL.item_no) Like 'NS*') AND _
((dbo_OELINHST_SQL.unit_price)>0));"

On the third line (dbo_ARCUSFIL_SQL.cus_no, dbo_OELINHST_SQL.item_desc_
1,) it gives me an error "End of line expected". My quotes all look
fine. Anyone see the error?

Thank you.
 
You have to add some quotes around each line.

sSQL = "SELECT Mid(Str([billed_dt]),2,4) AS [Year],
dbo_ARCUSFIL_SQL.slspsn_no, " & _
"dbo_ARCUSFIL_SQL.cus_no, dbo_OELINHST_SQL.item_desc_1,
dbo_OELINHST_SQL.item_no, " & _

There may be a limit so I often separate my code like
"dbo_ARCUSFIL_SQL.state, dbo_ARCUSFIL_SQL.zip, dbo_ARCUSFIL_SQL.country "
sSQL = sSQL & " FROM dbo_ARCUSFIL_SQL " & _
" LEFT JOIN dbo_OELINHST_SQL ON dbo_ARCUSFIL_SQL.cus_no =
dbo_OELINHST_SQL.cus_no "
sSQL = sSQL & " WHERE (((Mid(Str([billed_dt]),2,4))=2007) AND " & _
etc
 
Back
Top