原代码:
sql1 = "select .sellerid,count(*) as counts,sum(.k13) as k13,sum(gkfy.gkf01) as gkf01," _
& "sum(gkfy.gkf02) as gkf02,sum(gkfy.gkf03) as gkf03,sum(gkfy.gkf08) as gkf08,sum(gkfy.gkf09) as gkf09 " _
& "from left join gkfy on .lsxh=gkfy.lsxh and .datef=gkfy.datef " _
& "where .sellerid in (" & code & ") " _
& "and (.datef+.timef) between '" & DTPStar & "' and '" & DTPEnd & "' " _
& "group by .sellerid" _
& " Union all " _
& "select backup.sellerid,count(*) as counts,sum(backup.k13) as k13,sum(gkfybackup.gkf01) as gkf01," _
& "sum(gkfybackup.gkf02) as gkf02,sum(gkfybackup.gkf03) as gkf03,sum(gkfybackup.gkf08) as gkf08,sum(gkfybackup.gkf09) as gkf09 " _
& "from backup left join gkfybackup on backup.lsxh=gkfybackup.lsxh and backup.datef=gkfybackup.datef " _
& "where backup.sellerid in (" & code & ") " _
& "and (backup.datef+backup.timef) between '" & DTPStar & "' and '" & DTPEnd & "' " _
& "group by backup.sellerid"
Set rst1 = cn.OpenResultset(sql1, rdOpenStatic)
If Not rst1.EOF Then'加长加宽
Dim GKF01 As Variant
Dim GKF02 As Variant
Dim GKF03 As Variant
Dim GKF08 As Variant
Dim GKF09 As Variant
Dim addsql As String
Dim addrst As rdoResultset
addsql = "Select sum(gkfy.gkf01)*1.5 as gkf011,sum(gkfy.gkf02)*1.5 as gkf012,sum(gkfy.gkf03)*1.5 as gkf013,sum(gkfy.gkf08)*1.5 as gkf018,sum(gkfy.gkf09)*1.5 as gkf019 " _
& "From Mark " _
& " left join gkfy on .lsxh=gkfy.lsxh and .datef=gkfy.datef " _
& " where .skqk=1 and .sellerid in (" & code & ") and not(cldm is null) " _
& " and .datef + .timef between '" & DTPStar & "' " _
& " and '" & DTPEnd & "' and .carwidth>3.1 and .carlength>18 " _
set addrst = cn.OpenResultset(addsql, rdOpenStatic, rdConcurReadOnly, rdExecDirect)
While addrst.StillExecuting: DoEvents: Wend
GKF01 = Round(rst1!GKF01, 2) + Round(IIf(IsNull(addrst!GKF011), 0, Trim(addrst!GKF011)), 2)
GKF02 = Round(rst1!GKF02, 2) + Round(IIf(IsNull(addrst!GKF012), 0, Trim(addrst!GKF012)), 2)
GKF03 = Round(rst1!GKF03, 2) + Round(IIf(IsNull(addrst!GKF013), 0, Trim(addrst!GKF013)), 2)
GKF08 = Round(rst1!GKF08, 2) + Round(IIf(IsNull(addrst!GKF018), 0, Trim(addrst!GKF018)), 2)
GKF09 = Round(rst1!GKF09, 2) + Round(IIf(IsNull(addrst!GKF019), 0, Trim(addrst!GKF019)), 2)
addsql = "Select sum(gkfy.gkf01)*0.5 as gkf011,sum(gkfy.gkf02)*0.5 as gkf012,sum(gkfy.gkf03)*0.5 as gkf013,sum(gkfy.gkf08)*0.5 as gkf018,sum(gkfy.gkf09)*0.5 as gkf019 " _
& "From Mark " _
& " left join gkfy on .lsxh=gkfy.lsxh and .datef=gkfy.datef " _
& " where .skqk=1 and .sellerid in (" & code & ") and not(cldm is null) " _
& " and .datef + .timef between '" & DTPStar & "' " _
& " and '" & DTPEnd & "' and .carwidth>3.1 and .carlength<=18 "
Set addrst = cn.OpenResultset(addsql, rdOpenStatic, rdConcurReadOnly, rdExecDirect)
While addrst.StillExecuting: DoEvents: Wend
GKF01 = GKF01 + Round(IIf(IsNull(addrst!GKF011), 0, Trim(addrst!GKF011)), 2)
GKF02 = GKF02 + Round(IIf(IsNull(addrst!GKF012), 0, Trim(addrst!GKF012)), 2)
GKF03 = GKF03 + Round(IIf(IsNull(addrst!GKF013), 0, Trim(addrst!GKF013)), 2)
GKF08 = GKF08 + Round(IIf(IsNull(addrst!GKF018), 0, Trim(addrst!GKF018)), 2)
GKF09 = GKF09 + Round(IIf(IsNull(addrst!GKF019), 0, Trim(addrst!GKF019)), 2)
addrst.Close
''加长加宽'If Not rst1.EOF Then
For j = 1 To row
rst1.MoveFirst
For i = 1 To rst1.RowCount
If Mid(Cell1.GetCellString(1, 4 + j, 0), 1, InStr(Cell1.GetCellString(1, 4 + j, 0), "-") - 1) = Trim(rst1!SellerID) Then
Cell1.SetFormula 3, 4 + j, 0, Round(Cell1.GetCellString(3, 4 + j, 0), 2) + Round(GKF01, 2)
Cell1.SetFormula 5, 4 + j, 0, Round(Cell1.GetCellString(5, 4 + j, 0), 2) + Round(GKF08, 2)
Cell1.SetFormula 7, 4 + j, 0, Round(Cell1.GetCellString(7, 4 + j, 0), 2) + Round(GKF09, 2)
Cell1.SetFormula 9, 4 + j, 0, Round(Cell1.GetCellString(9, 4 + j, 0), 2) + Round(GKF03, 2)
Cell1.SetFormula 10, 4 + j, 0, Round(Cell1.GetCellString(10, 4 + j, 0), 2) + Round(GKF02, 2)
Cell1.SetFormula 14, 4 + j, 0, Round(rst1!counts, 2)
Cell1.SetFormula 17, 4 + j, 0, Round(Cell1.GetCellString(17, 4 + j, 0), 2) + Round(rst1!K13, 2)
Exit For
End If
rst1.MoveNext
Next
Next
End If
''加长加宽
。中间为新加代码,出现错误结果
''加长加宽
各位大哥帮帮了了,万分感激
sql1 = "select .sellerid,count(*) as counts,sum(.k13) as k13,sum(gkfy.gkf01) as gkf01," _
& "sum(gkfy.gkf02) as gkf02,sum(gkfy.gkf03) as gkf03,sum(gkfy.gkf08) as gkf08,sum(gkfy.gkf09) as gkf09 " _
& "from left join gkfy on .lsxh=gkfy.lsxh and .datef=gkfy.datef " _
& "where .sellerid in (" & code & ") " _
& "and (.datef+.timef) between '" & DTPStar & "' and '" & DTPEnd & "' " _
& "group by .sellerid" _
& " Union all " _
& "select backup.sellerid,count(*) as counts,sum(backup.k13) as k13,sum(gkfybackup.gkf01) as gkf01," _
& "sum(gkfybackup.gkf02) as gkf02,sum(gkfybackup.gkf03) as gkf03,sum(gkfybackup.gkf08) as gkf08,sum(gkfybackup.gkf09) as gkf09 " _
& "from backup left join gkfybackup on backup.lsxh=gkfybackup.lsxh and backup.datef=gkfybackup.datef " _
& "where backup.sellerid in (" & code & ") " _
& "and (backup.datef+backup.timef) between '" & DTPStar & "' and '" & DTPEnd & "' " _
& "group by backup.sellerid"
Set rst1 = cn.OpenResultset(sql1, rdOpenStatic)
If Not rst1.EOF Then'加长加宽
Dim GKF01 As Variant
Dim GKF02 As Variant
Dim GKF03 As Variant
Dim GKF08 As Variant
Dim GKF09 As Variant
Dim addsql As String
Dim addrst As rdoResultset
addsql = "Select sum(gkfy.gkf01)*1.5 as gkf011,sum(gkfy.gkf02)*1.5 as gkf012,sum(gkfy.gkf03)*1.5 as gkf013,sum(gkfy.gkf08)*1.5 as gkf018,sum(gkfy.gkf09)*1.5 as gkf019 " _
& "From Mark " _
& " left join gkfy on .lsxh=gkfy.lsxh and .datef=gkfy.datef " _
& " where .skqk=1 and .sellerid in (" & code & ") and not(cldm is null) " _
& " and .datef + .timef between '" & DTPStar & "' " _
& " and '" & DTPEnd & "' and .carwidth>3.1 and .carlength>18 " _
set addrst = cn.OpenResultset(addsql, rdOpenStatic, rdConcurReadOnly, rdExecDirect)
While addrst.StillExecuting: DoEvents: Wend
GKF01 = Round(rst1!GKF01, 2) + Round(IIf(IsNull(addrst!GKF011), 0, Trim(addrst!GKF011)), 2)
GKF02 = Round(rst1!GKF02, 2) + Round(IIf(IsNull(addrst!GKF012), 0, Trim(addrst!GKF012)), 2)
GKF03 = Round(rst1!GKF03, 2) + Round(IIf(IsNull(addrst!GKF013), 0, Trim(addrst!GKF013)), 2)
GKF08 = Round(rst1!GKF08, 2) + Round(IIf(IsNull(addrst!GKF018), 0, Trim(addrst!GKF018)), 2)
GKF09 = Round(rst1!GKF09, 2) + Round(IIf(IsNull(addrst!GKF019), 0, Trim(addrst!GKF019)), 2)
addsql = "Select sum(gkfy.gkf01)*0.5 as gkf011,sum(gkfy.gkf02)*0.5 as gkf012,sum(gkfy.gkf03)*0.5 as gkf013,sum(gkfy.gkf08)*0.5 as gkf018,sum(gkfy.gkf09)*0.5 as gkf019 " _
& "From Mark " _
& " left join gkfy on .lsxh=gkfy.lsxh and .datef=gkfy.datef " _
& " where .skqk=1 and .sellerid in (" & code & ") and not(cldm is null) " _
& " and .datef + .timef between '" & DTPStar & "' " _
& " and '" & DTPEnd & "' and .carwidth>3.1 and .carlength<=18 "
Set addrst = cn.OpenResultset(addsql, rdOpenStatic, rdConcurReadOnly, rdExecDirect)
While addrst.StillExecuting: DoEvents: Wend
GKF01 = GKF01 + Round(IIf(IsNull(addrst!GKF011), 0, Trim(addrst!GKF011)), 2)
GKF02 = GKF02 + Round(IIf(IsNull(addrst!GKF012), 0, Trim(addrst!GKF012)), 2)
GKF03 = GKF03 + Round(IIf(IsNull(addrst!GKF013), 0, Trim(addrst!GKF013)), 2)
GKF08 = GKF08 + Round(IIf(IsNull(addrst!GKF018), 0, Trim(addrst!GKF018)), 2)
GKF09 = GKF09 + Round(IIf(IsNull(addrst!GKF019), 0, Trim(addrst!GKF019)), 2)
addrst.Close
''加长加宽'If Not rst1.EOF Then
For j = 1 To row
rst1.MoveFirst
For i = 1 To rst1.RowCount
If Mid(Cell1.GetCellString(1, 4 + j, 0), 1, InStr(Cell1.GetCellString(1, 4 + j, 0), "-") - 1) = Trim(rst1!SellerID) Then
Cell1.SetFormula 3, 4 + j, 0, Round(Cell1.GetCellString(3, 4 + j, 0), 2) + Round(GKF01, 2)
Cell1.SetFormula 5, 4 + j, 0, Round(Cell1.GetCellString(5, 4 + j, 0), 2) + Round(GKF08, 2)
Cell1.SetFormula 7, 4 + j, 0, Round(Cell1.GetCellString(7, 4 + j, 0), 2) + Round(GKF09, 2)
Cell1.SetFormula 9, 4 + j, 0, Round(Cell1.GetCellString(9, 4 + j, 0), 2) + Round(GKF03, 2)
Cell1.SetFormula 10, 4 + j, 0, Round(Cell1.GetCellString(10, 4 + j, 0), 2) + Round(GKF02, 2)
Cell1.SetFormula 14, 4 + j, 0, Round(rst1!counts, 2)
Cell1.SetFormula 17, 4 + j, 0, Round(Cell1.GetCellString(17, 4 + j, 0), 2) + Round(rst1!K13, 2)
Exit For
End If
rst1.MoveNext
Next
Next
End If
''加长加宽
。中间为新加代码,出现错误结果
''加长加宽
各位大哥帮帮了了,万分感激
楼主您的代码连接不正确。
正确的连接方法是以下。
sql1 = "select .sellerid,count(*) as counts,sum(.k13) as k13,sum(gkfy.gkf01) as gkf01," & _
"sum(gkfy.gkf02) as gkf02,sum(gkfy.gkf03) as gkf03,sum(gkfy.gkf08) as gkf08,sum(gkfy.gkf09) as gkf09 " & _
"from left join gkfy on .lsxh=gkfy.lsxh and .datef=gkfy.datef " & _
"where .sellerid in (" & code & ") " & _
"and (.datef+.timef) between '" & DTPStar & "' and '" & DTPEnd & "' " & _
"group by .sellerid" & _
" Union all " & _
.............
"group by backup.sellerid"
1、您的代码里没有做错误处理,当您要查的日期内没有数据时就会NULL出错。
2、您的代码即使用了联接LEFT JOIN 又使用了联合查询,很容易出错的。
3、当您要进行复杂查询时,可以考虑使用临时表。