我在VBA中的部分SQL代码有一个“运行时错误'13':类型不匹配”。
错误所指向的代码是:
SQL = SQL & " (select unique sv.gps_planshpdate from oes_delsegview sv, oes_opos op, oes_oposdelseg ds, oes_nrbom nb, ncf_comppart cp where cp.item_part_no = pd.part_no and nb.c_catnr = cp.catnr and nb.c_prodtyp = cp.prodtyp and nb.c_packtyp = cp.packtyp and nb.c_vernr = cp.vernr " & _
SQL = SQL & " and sv.ordnr = ds.ordnr and sv.posnr = ds.posnr and sv.catnr = nb.p_catnr and sv.prodtyp = nb.p_prodtyp and sv.packtyp = nb.p_packtyp and op.ordnr = ds.ordnr and op.posnr = ds.posnr and op.catnr = nb.p_catnr and op.prodtyp = nb.p_prodtyp and op.packtyp = nb.p_packtyp " & _
SQL = SQL & " and op.vernr = nb.p_vernr and nb.active = 'Y' and op.ol_typ in ('XX','CO') and sv.gps_planshpdate = " & _
SQL = SQL & " (select max(sv1.gps_planshpdate) from oes_delsegview sv1, oes_opos op1, oes_oposdelseg ds1, oes_nrbom nb1, ncf_comppart cp1 where cp1.item_part_no = cp.item_part_no and nb1.c_catnr = cp1.catnr " & _
SQL = SQL & " and nb1.c_prodtyp = cp1.prodtyp and nb1.c_packtyp = cp1.packtyp and nb1.c_vernr = cp1.vernr and sv1.ordnr = ds1.ordnr and sv1.posnr = ds1.posnr and sv1.catnr = nb1.p_catnr and sv1.prodtyp = nb1.p_prodtyp and sv1.packtyp = nb1.p_packtyp " & _
SQL = SQL & " and op1.ordnr = ds1.ordnr and op1.posnr = ds1.posnr and op1.catnr = nb1.p_catnr and op1.prodtyp = nb1.p_prodtyp and op1.packtyp = nb1.p_packtyp and op1.vernr = nb1.p_vernr and nb1.active = 'Y' and sv1.ord_o_status = '9' and op1.ol_typ in ('XX','CO'))), " 'last_ship_date_manufacturing
任何想法如何运行此代码而不会出现“类型不匹配”错误?
谢谢
当我评估您的表达式时,它返回FALSE
,这肯定是导致不匹配错误的原因,因为SQL需要一个字符串查询。
问题出在您最初的串联方法中,这是错误地组合了两件事。
如果您想做:
Sql = Sql & " some expression"
Sql = Sql & " some other expression"
'Etc.
需要注意的是在上面的,我也不要继续行了,像你这样:
Sql = Sql & " some expression" & _
Sql = Sql & " some other expression"
那会引发一个错误。因此可视化它:
Sql = Sql & " A " & Sql = Sql & " B "
或者:
Sql = ((Sql & " A " & Sql) = (Sql & " B "))
错误的产生是因为该表达式在逻辑上求和False
:该赋值语句的右侧是一个等效语句,并且由于等效语句中的两个表达式不相等,因此只能返回FALSE
。
除了纠正对行继续字符的滥用以外,我没有对查询表达式进行任何更改:
Dim SQL As String
SQL = SQL & " (select unique sv.gps_planshpdate from oes_delsegview sv, " & _
"oes_opos op, oes_oposdelseg ds, oes_nrbom nb, ncf_comppart cp " & _
"where cp.item_part_no = pd.part_no and nb.c_catnr = cp.catnr and " & _
"nb.c_prodtyp = cp.prodtyp and nb.c_packtyp = cp.packtyp and nb.c_vernr = cp.vernr " & _
" and sv.ordnr = ds.ordnr and sv.posnr = ds.posnr and sv.catnr = nb.p_catnr " & _
"and sv.prodtyp = nb.p_prodtyp and sv.packtyp = nb.p_packtyp and op.ordnr = ds.ordnr " & _
"and op.posnr = ds.posnr and op.catnr = nb.p_catnr and op.prodtyp = nb.p_prodtyp and " & _
"op.packtyp = nb.p_packtyp " & _
" and op.vernr = nb.p_vernr and nb.active = 'Y' and op.ol_typ in ('XX','CO') and " & _
"sv.gps_planshpdate = " & _
" (select max(sv1.gps_planshpdate) from oes_delsegview sv1, oes_opos op1, " & _
"oes_oposdelseg ds1, oes_nrbom nb1, ncf_comppart cp1 where cp1.item_part_no = " & _
"cp.item_part_no and nb1.c_catnr = cp1.catnr " & _
" and nb1.c_prodtyp = cp1.prodtyp and nb1.c_packtyp = cp1.packtyp and " & _
"nb1.c_vernr = cp1.vernr and sv1.ordnr = ds1.ordnr and sv1.posnr = ds1.posnr and " & _
"sv1.catnr = nb1.p_catnr and sv1.prodtyp = nb1.p_prodtyp and sv1.packtyp = nb1.p_packtyp " & _
" and op1.ordnr = ds1.ordnr and op1.posnr = ds1.posnr and op1.catnr = " & _
"nb1.p_catnr and op1.prodtyp = nb1.p_prodtyp and op1.packtyp = nb1.p_packtyp and op1.vernr " & _
"= nb1.p_vernr and nb1.active = 'Y' and sv1.ord_o_status = '9' and op1.ol_typ in ('XX','CO'))), " 'last_ship_date_manufacturing
Debug.Print SQL
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句