主题：数据回填

老师您好！

  我想把物流表的数据回填到费用表中，我的代码只能回填客户不为空的数据，客户为空的不回填，我不知道问题出在哪，请指教！谢谢！

Dim dtp1 As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker1")
Dim dtp2 As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker2")

Dim y1 As Integer=year(dtp1.value)
Dim m1 As Integer = month(dtp1.value)
Dim d1 As Integer = day(dtp1.value)

Dim y2 As Integer=year(dtp2.value)
Dim m2 As Integer = month(dtp2.value)
Dim d2 As Integer = day(dtp2.value)

Dim dt1 As New Date(y1, m1, d1)
Dim dt2 As New Date(y2, m2, d2)
Dim dt3 As New Date(y1, m1, 1)
Dim dt4 As New Date(y1, m1, Date.DaysInMonth(y1,m1))

 

Dim tje,tje1,tje2,tgh1,tgh2,tgh3,tgh4,tgh,tzb1,tzb2 As Double

For Each r As Row In Tables("物流表").rows
   
    Dim xm As String=r("xmdm")
    Dim kh As String=r("客户")
    Dim dl As String=r("大类代码")
    Dim edm As String=r("二级代码")
    Dim dr As DataRow = DataTables("物流表").find("发货日期>=# " & dt1 & "# and 发货日期<= #"  & dt2 & "# And xmdm='" & xm & "'and 客户='" & kh & "'")
   
    If dr IsNot Nothing Then
       
        If dr("客户") IsNot Nothing
           
            Dim fr As DataRow = DataTables("fyb").find("日期>=# " & dt1 & "# and 日期<= #"  & dt2 & "# And xmdm='" & xm & "'and 客户='" & kh & "'")
            If fr Is Nothing Then
                fr=DataTables("fyb").AddNew()
               
                If  edm="a041" Then
                    tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm='" & xm & "'and 客户='" & kh & "'and [二级代码]='a041'")
                    fr("日期")=dr("发货日期")
                    fr("xmdm") =xm
                   
                    fr("客户") =dr("客户")
                    fr("大类代码")=dr("大类代码")
                    fr("大类名称") =dr("大类名称")
                    fr("二级名称")=dr("二级名称")
                    fr("二级代码") =dr("二级代码")
                    fr("项目名称") =dr("项目名称")
                    fr("lb")="f"
                    fr("金额") =tje
                End If
               
                If edm="a042" Then

                    tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm='" & xm & "' and 客户='" & kh & "'and [二级代码]='a042'")
                    fr("日期")=dr("发货日期")
                    fr("xmdm") =xm
                   
                    fr("客户") =dr("客户")
                    fr("大类代码")=dr("大类代码")
                    fr("大类名称") =dr("大类名称")
                    fr("二级名称")=dr("二级名称")
                    fr("二级代码") =dr("二级代码")
                    fr("项目名称") =dr("项目名称")
                    fr("lb")="w"
                    fr("金额") =tje
                End If
              
               
                If  edm<>"a041" And edm<>"a042" Then
                    tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm='" & xm & "' and 客户='" & kh & "'and [二级代码]<>'a041' and [二级代码]<>'a042'")
                    tgh1=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)='c'" & " and 客户='" & kh & "' And 发货日期 Is not null")  '合计层压木产量
                    tgh2=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)='z'" & " and 客户='" & kh & "' and 发货日期 Is not null")  '合计纸板产量
                    tgh3=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)='f'" & " and 客户='" & kh & "' And 发货日期 Is not null")  '合计辅料产量
                    tgh4=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)='w'" & " and 客户='" & kh & "' and 发货日期 Is not null")  '合计外协产量
                   
                    tgh=tgh1+tgh2
                    tzb1=tgh1/tgh
                    tzb2=tgh2/tgh
                    tje1=tje*tzb1
                    tje2=tje*tzb2
                    messagebox.show("客户:" & kh & "层压木本月产量:" & tgh1 & "纸板产量:" & tgh2 & "总金额:" & tje)
                   
                   
                    fr("日期")=dr("发货日期")
                    fr("xmdm") =xm
                   
                    fr("客户") =dr("客户")
                    fr("大类代码")=dr("大类代码")
                    fr("大类名称") =dr("大类名称")
                    fr("二级名称")=dr("二级名称")
                    fr("二级代码") =dr("二级代码")
                    fr("项目名称") =dr("项目名称")
                    If tje1<>0 And tje2<>0 Then
                       
                       
                        fr("lb")="c"
                        fr("金额") =tje1
                        Dim wr As DataRow=DataTables("fyb").AddNew
                        wr("日期")=dr("发货日期")
                        wr("xmdm") =xm
                       
                        wr("客户") =dr("客户")
                        wr("大类代码")=dr("大类代码")
                        wr("大类名称") =dr("大类名称")
                        wr("二级名称")=dr("二级名称")
                        wr("二级代码") =dr("二级代码")
                        wr("项目名称") =dr("项目名称")
                        wr("lb")="z"
                        wr("金额") =tje2
                    End If
                    If tje1=0 And tje2<>0
                        fr("日期")=dr("发货日期")
                        fr("xmdm") =xm
                       
                        fr("客户") =dr("客户")
                        fr("大类代码")=dr("大类代码")
                        fr("大类名称") =dr("大类名称")
                        fr("二级名称")=dr("二级名称")
                        fr("二级代码") =dr("二级代码")
                        fr("项目名称") =dr("项目名称")
                       
                       
                        fr("lb")="z"
                        fr("金额") =tje2
                    End If
                    If tje1<>0 And tje2=0
                        fr("xmdm") =xm
                       
                        fr("客户") =dr("客户")
                        fr("大类代码")=dr("大类代码")
                        fr("大类名称") =dr("大类名称")
                        fr("二级名称")=dr("二级名称")
                        fr("二级代码") =dr("二级代码")
                        fr("项目名称") =dr("项目名称")
                        fr("lb")="c"
                        fr("金额") =tje1
                       
                    End If
                    If tgh1=0 And tgh2=0 And tgh3<>0
                     tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm='" & xm & "' and 客户='" & kh & "'and [二级代码]<>'a041' and [二级代码]<>'a042'")

                    tgh3=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)='f'" & " and 客户='" & kh & "' And 发货日期 Is not null")  '合计辅料产量
                    tgh4=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)='w'" & " and 客户='" & kh & "' and 发货日期 Is not null")  '合计外协
                        fr("日期")=dr("发货日期")
                        fr("xmdm") =xm
                       
                        fr("客户") =dr("客户")
                        fr("大类代码")=dr("大类代码")
                        fr("大类名称") =dr("大类名称")
                        fr("二级名称")=dr("二级名称")
                        fr("二级代码") =dr("二级代码")
                        fr("项目名称") =dr("项目名称")
                        fr("lb")="f"
                        fr("金额") =tje
                    End If
                    If tgh1=0 And tgh2=0 And tgh4<>0
                        fr("日期")=dr("发货日期")
                        fr("xmdm") =xm
                       
                        fr("客户") =dr("客户")
                        fr("大类代码")=dr("大类代码")
                        fr("大类名称") =dr("大类名称")
                        fr("二级名称")=dr("二级名称")
                        fr("二级代码") =dr("二级代码")
                        fr("项目名称") =dr("项目名称")
                        fr("lb")="w"
                        fr("金额") =tje
                    End If
                   
                   
                   
                End If
            End If
        Else If dr.IsNull("客户")
            Dim fr As DataRow = DataTables("fyb").find("日期>=# " & dt1 & "# and 日期<= #"  & dt2 & "# And xmdm='" & xm & "'and 客户 isnull")
           
            If fr Is Nothing Then
                fr=DataTables("fyb").AddNew()
                If dr("大类代码")="a" And edm<>"a041" And edm<>"a042" Then
                   
                    tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm='" & xm & "'[大类代码]='a' and [二级代码]<>'a041' and [二级代码]<>'042'and 客户 isnull")
                    fr("日期")=dr("发货日期")
                    fr("客户")=Nothing
                    fr("xmdm") =xm
                    fr("大类代码")=dr("大类代码")
                    fr("大类名称") =dr("大类名称")
                    fr("二级名称")=dr("二级名称")
                    fr("二级代码") =dr("二级代码")
                    fr("项目名称") =dr("项目名称")
                    fr("lb")="t"
                    fr("金额") =tje
                End If
               
                If dr("大类代码")="b" Then
                    tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm='" & xm & "'[大类代码]='b'and 客户 isnull")
  
                    fr("日期")=dr("发货日期")
                    fr("xmdm") =xm
                    fr("客户")=Nothing
                   
                    fr("大类代码")=dr("大类代码")
                    fr("大类名称") =dr("大类名称")
                    fr("二级名称")=dr("二级名称")
                    fr("二级代码") =dr("二级代码")
                    fr("项目名称") =dr("项目名称")
                    fr("lb")="t"
                    fr("金额") =tje
                   
                End If
            End If
        End If
    End If
Next
messagebox.show("回填完毕,请认真核对数据!","提示:")
MainTable=Tables("fyb")