VB开发的ERP系统

                    If Trim(.LrText(1).Text) <> "" Then             '所属部门
                        Str_Temp = Str_Temp & " and gdzc_card.DeptCode like '" & Trim(.LrText(1).Tag) & "%'"
                        Str_temp1 = Str_temp1 & " and gdzc_card.DeptCode like '" & Trim(.LrText(1).Tag) & "%'"
                        Sql_Str = Sql_Str & " and Gdzc_Variation.deptNew like '" & Trim(.LrText(1).Tag) & "%'"
                        Lbl_Dept.Caption = Trim(.LrText(1).Text)
                    Else
                        Lbl_Dept.Caption = "所有部门"
                    End If
                Case 3
                    If Trim(.LrText(2).Text) <> "" Then             '卡片编号
                        Str_Temp = Str_Temp & " and gdzc_Variation.CardCode='" & Trim(.LrText(2).Text) & "'"
                        Str_temp1 = Str_temp1 & " and Gdzc_Card.CardCode='" & Trim(.LrText(2).Text) & "'"
                        Sql_Str = Sql_Str & " and Gdzc_Variation.cardcode='" & Trim(.LrText(2).Text) & "'"
                    End If
                Case 4
                    If Trim(.Com_Year(0).Text) <> "" Then           '起始会计期间
                        Str_Temp = Str_Temp & " and Gdzc_Variation.year>=" & LeftChar(Trim(.Com_Year(0).Text)) & " and Gdzc_Variation.period>=" & RightChar(Trim(.Com_Year(0).Text))
                        Str_temp1 = Str_temp1 & " and Gdzc_DetailedForm.year>=" & LeftChar(Trim(.Com_Year(0).Text)) & " and Gdzc_DetailedForm.period>=" & RightChar(Trim(.Com_Year(0).Text))
                    End If
                Case 5
                    If Trim(.Com_Year(1).Text) <> "" Then           '终止会计期间
                        Str_Temp = Str_Temp & " and Gdzc_Variation.year<=" & LeftChar(Trim(.Com_Year(1).Text)) & " and Gdzc_Variation.period<=" & RightChar(Trim(.Com_Year(1).Text))
                        Str_temp1 = Str_temp1 & " and Gdzc_DetailedForm.year<=" & LeftChar(Trim(.Com_Year(1).Text)) & " and Gdzc_DetailedForm.period<=" & RightChar(Trim(.Com_Year(1).Text))
                    End If
            End Select
        Next Jsqte
    End With
    
    '当用户要求显示“期间合计”时，为合计查询字符串赋值
    If Book_Parti_Search.Opt_Yes = True Then        '输出期间合计
        SqlStr1 = "SELECT DISTINCT Gdzc_DetailedForm.Period FROM Gdzc_DetailedForm LEFT OUTER JOIN " _
            & "Gdzc_Card ON Gdzc_DetailedForm.CardCode = Gdzc_Card.CardCode LEFT OUTER JOIN " _
            & "Gdzc_Sort ON Gdzc_DetailedForm.FASortCode = Gdzc_Sort.FASortCode LEFT OUTER JOIN " _
            & "Gy_Department ON Gdzc_Card.DeptCode = Gy_Department.DeptCode " _
            & Str_temp1 & " and (Gdzc_DetailedForm.FAValueEnd > 0) "
        Set rstemp = Cw_DataEnvi.DataConnect.Execute(SqlStr1)
        CxbbGrid.Rows = CxbbGrid.FixedRows
        CxbbGrid.Rows = CxbbGrid.FixedRows + rstemp.RecordCount
        rstemp.Close
        Set rstemp = Nothing
    
        SqlStr1 = "SELECT Gy_Department.DeptName AS DeptName, Gdzc_Sort.FASortName AS FASortName, " _
            & "Gdzc_DetailedForm.FAValueStart, Gdzc_DetailedForm.DeprSumStart, " _
            & "Gdzc_DetailedForm.FAValueEnd, Gdzc_DetailedForm.DeprSUmEnd, " _
            & "Gdzc_DetailedForm.DeprValue FROM Gdzc_DetailedForm LEFT OUTER JOIN " _
            & "Gdzc_Card ON Gdzc_DetailedForm.CardCode = Gdzc_Card.CardCode LEFT OUTER JOIN " _
            & "Gdzc_Sort ON Gdzc_DetailedForm.FASortCode = Gdzc_Sort.FASortCode LEFT OUTER JOIN " _
            & "Gy_Department ON Gdzc_Card.DeptCode = Gy_Department.DeptCode "
        SqlStr1 = SqlStr1 & Trim(Str_temp1)
    End If
    
    Sqlstr = "SELECT Gdzc_Variation.CardCode AS CardCode, Gdzc_Variation.[Year] AS Year, " _
        & "Gdzc_Variation.Period AS Period, Gdzc_Variation.VariDate AS VariDate, " _
        & "Gy_Department.DeptName AS DeptName, Gdzc_Sort.FASortName AS FASortName, " _
        & "Gdzc_Variation.FAValueOld AS FAValueOld,Gdzc_Variation.FAValueNew AS FAValueNew, " _
        & "Gdzc_Variation.SumDeprOld AS SumDeprOld,Gdzc_Variation.SumDeprNew AS SumDeprNew, Gdzc_Card.FACode,Gdzc_Card.FAName, " _
        & "Gdzc_Variation.VariationReason AS VariationReaSon FROM Gy_Department RIGHT OUTER JOIN " _
        & "Gdzc_Card ON Gy_Department.DeptCode = Gdzc_Card.DeptCode LEFT OUTER JOIN " _
        & "Gdzc_Sort ON Gdzc_Card.FASortCode = Gdzc_Sort.FASortCode RIGHT OUTER JOIN " _
        & "Gdzc_Variation ON Gdzc_Card.CardCode = Gdzc_Variation.CardCode "
    Sqlstr = Sqlstr & Trim(Str_Temp) & " order by Gdzc_Variation.year,Gdzc_Variation.period,gdzc_Variation.cardcode"
    Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
    With Rec_Query
        If Book_Parti_Search.Opt_No = True Then CxbbGrid.Rows = CxbbGrid.FixedRows
        CxbbGrid.Rows = CxbbGrid.Rows + .RecordCount
        Jsqte = CxbbGrid.FixedRows
    
        Do While Not .EOF
            If Jsqte >= CxbbGrid.Rows Then
                CxbbGrid.AddItem ""
            End If
            
            '填充列表
            CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(!Year & "")                                            '会计年度
            CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(Format(!Period, "00") & "")                                         '会计期间
            CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Trim(!varidate & "")                                        '业务日期
            CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(!CardCode & "")                                        '卡片编号
            CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(!FACode & "")                                          '资产编号
            CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(!FAName & "")                                          '资产名称
            CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(!VariationReason & "")                                 '摘要
            If Val(!FAValueNew) > Val(!FAValueOld) Then
                CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(Abs(Val(!FAValueNew) - Val(!FAValueOld)) & "")     '资产原值借方
            Else
                CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(Abs(Val(!FAValueNew) - Val(!FAValueOld)) & "")     '资产原值贷方
            End If
            CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(!FAValueNew & "")                                      '资产原值余额
            If Val(!SumDeprNew) > Val(!SumDeprOld) Then
                CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Trim(Abs(Val(!SumDeprNew) - Val(!SumDeprOld)) & "")     '累计折旧贷方
            Else
                CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Trim(Abs(Val(!SumDeprNew) - Val(!SumDeprOld)) & "")     '累计折旧借方
            End If
            If Val(!SumDeprNew) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Trim(!SumDeprNew & "")        '累计折旧余额
            If Val(Val(!FAValueNew) - Val(!SumDeprNew)) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = Trim(Val(Val(!FAValueNew) - Val(!SumDeprNew)) & "")   '净资产
            CxbbGrid.RowHeight(Jsqte) = Sjhgd
            .MoveNext
            Jsqte = Jsqte + 1
             
            '本期合计处理
            If Book_Parti_Search.Opt_Yes = True Then
                If Not .EOF Then
                    If Trim(!Period) <> Val(Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("002", GridStr(), Szzls)))) Then
                        Set rstemp = New ADODB.Recordset
                        rstemp.Open SqlStr1 & " and gdzc_detailedform.Year=" & Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls))) & " and gdzc_detailedform.Period=" & Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("002", GridStr(), Szzls))), Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                        While Not rstemp.EOF
                            CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = "本期合计"
                            If Val(rstemp!FAValueEnd) - Val(rstemp!FAValuestart) > 0 Then
                                CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls))) + Val(Abs(Val(rstemp!FAValueEnd) - Val(rstemp!FAValuestart)))
                            Else
                                If Val(Abs(Val(rstemp!FAValueEnd) - Val(rstemp!FAValuestart))) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Abs(Val(rstemp!FAValueEnd) - Val(rstemp!FAValuestart))
                            End If

                            CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls))) + Val(rstemp!FAValueEnd)
                            If Val(Val(rstemp!DeprSumEnd) - Val(rstemp!DeprValue)) < Val(rstemp!DeprSumStart) Then
                                CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls))) + Val(rstemp!DeprValue) + Val(rstemp!DeprSumStart) - Val(rstemp!DeprSumEnd)
                            End If
                            If Val(rstemp!DeprValue) > 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls))) + Val(rstemp!DeprValue)
                            CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls))) + Val(rstemp!DeprSumEnd)
                            CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)))
                            rstemp.MoveNext
                        Wend
                        rstemp.Close
                        Set rstemp = Nothing
                        
                        For Row_Num = 0 To CxbbGrid.Cols - 1
                            CxbbGrid.Cell(flexcpBackColor, Jsqte, Row_Num) = &HFFFF00
                        Next Row_Num
                        
                        Jsqte = Jsqte + 1
                    End If
                Else
                                        
                    Set rstemp = New ADODB.Recordset
                    rstemp.Open "SELECT SUM(FAValueOld) AS FAValueOld, SUM(FAValueNew) AS FAValueNew,SUM(SumDeprOld) AS SumDeprOld, SUM(SumDeprNew) AS SumDeprNew FROM Gdzc_Variation where Year=" & Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls))) & " and Period=" & Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("002", GridStr(), Szzls))) & Sql_Str, Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                    If Not rstemp.EOF Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = "本期合计"
                        If Val(rstemp!FAValueNew & "") > Val(rstemp!FAValueOld & "") Then
                            CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Val(rstemp!FAValueNew & "") - Val(rstemp!FAValueOld & "")
                        Else
                            CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Val(rstemp!FAValueNew & "") - Val(rstemp!FAValueOld & "")
                        End If
                        If Val(rstemp!SumDeprNew & "") > Val(rstemp!SumDeprOld & "") Then
                            CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Val(rstemp!SumDeprNew & "") - Val(rstemp!SumDeprOld & "")
                        Else
                            CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Val(rstemp!SumDeprNew & "") - Val(rstemp!SumDeprOld & "")
                        End If
                    End If
                    rstemp.Close
                    Set rstemp = Nothing
                    
                    Set rstemp = New ADODB.Recordset
                    rstemp.Open "SELECT SUM(Gdzc_DetailedForm.FAValueStart) AS FAValue, " _
                            & "Sum (Gdzc_DetailedForm.DeprSumStart) + Sum(Gdzc_DetailedForm.DeprValue) AS DeprSum " _
                            & "FROM Gdzc_Card RIGHT OUTER JOIN Gdzc_DetailedForm ON " _
                            & "Gdzc_Card.CardCode = Gdzc_DetailedForm.CardCode " _
                            & Str_temp1 _
                            & " and Gdzc_DetailedForm.Year=" & Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls))) & " and Gdzc_DetailedForm.Period=" & Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("002", GridStr(), Szzls))), _
                            Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                    If Not rstemp.EOF Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls))) + Val(rstemp!FAValue)
                        CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls))) + Val(Trim(rstemp!DeprSum & "")) - Val(CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)))
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)))
                    End If
                    rstemp.Close
                    Set rstemp = Nothing
                    
                    For Row_Num = 0 To CxbbGrid.Cols - 1
                        CxbbGrid.Cell(flexcpBackColor, Jsqte, Row_Num) = &HFFFF00
                    Next Row_Num
                    
                    Jsqte = Jsqte + 1
                End If
            End If
        Loop
    End With
    
    '为零时清空单元格
    Call Txt_Clear
    
End Sub

Private Sub bbyl(bbylte As Boolean)                    '报表打印预览
    
    Dim Bbzbt$, Bbxbt() As String, bbxbtzzxs() As Integer, Bbxbtgs As Integer
    Dim Bbbwh() As String, Bbbwhzzxs() As Integer, Bbbwhgs As Integer
    Bbxbtgs = 1                                          '报 表 小 标 题 行 数
    Bbbwhgs = 0                                          '报 表 表 尾 行 数
    ReDim Bbxbt(1 To Bbxbtgs)
    ReDim bbxbtzzxs(1 To Bbxbtgs)
    
    If Bbbwhgs <> 0 Then
        ReDim Bbbwh(1 To Bbbwhgs)
        ReDim Bbbwhzzxs(1 To Bbbwhgs)
    End If
    
    Bbzbt = ReportTitle
    Bbxbt(1) = " "
    bbxbtzzxs(1) = 0                                     '报表行组织形式(0-居左 1-居中 2-居右)
    
    Bbxbt(1) = Space(2) + Fun_FormatOutPut(Trim(Label1.Caption) & Trim(Lbl_Sort.Caption), 35)
    Bbxbt(1) = Bbxbt(1) + Trim(Trim(Label3.Caption) & Trim(Lbl_Dept.Caption))
    Call Scyxsjb(CxbbGrid)                               '生成报表数据
    Call Scdybb(Dyymctbl, Bbzbt, Bbxbt(), bbxbtzzxs(), Bbxbtgs, Bbbwh(), Bbbwhzzxs(), Bbbwhgs, bbylte)
  
    If Not bbylte Then
        Unload DY_Tybbyldy
    End If

End Sub

'*****************************************************
'[>>自定义函数
'取右字符串
Function RightChar(str As String) As String

    If str = "" Then Exit Function
    
    Dim i As Integer
    
    i = InStrRev(str, ".")
    RightChar = Mid(str, i + 1)
    
End Function

'取左字符串
Function LeftChar(str As String) As String

    If str = "" Then Exit Function
    
    Dim i As Integer
    
    i = InStr(str, ".")
    LeftChar = Mid(str, 1, i - 1)
    
End Function

'为零时清空单元格
Function Txt_Clear()

    Dim Row_Integer, Col_Integer As Integer         '行数和列数变量
    
    For Row_Integer = CxbbGrid.FixedRows To CxbbGrid.Rows - 1
        For Col_Integer = 7 To CxbbGrid.Cols - 1
            If Format(Val(CxbbGrid.TextMatrix(Row_Integer, Col_Integer)), "0.00") = 0# Then
                CxbbGrid.TextMatrix(Row_Integer, Col_Integer) = ""
            End If
        Next Col_Integer
    Next Row_Integer
    
End Function
'<<]
'****************************************************