Foxtable(狐表)用户栏目专家坐堂 → 再次向蓝老师请教多栏式会计账问题


  共有3822人关注过本帖树形打印复制链接

主题:再次向蓝老师请教多栏式会计账问题

帅哥哟,离线,有人找我吗?
13315253800
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:二尾狐 帖子:534 积分:3874 威望:0 精华:0 注册:2018/1/3 16:03:00
再次向蓝老师请教多栏式会计账问题  发帖心情 Post By:2022/5/16 17:15:00 [只看该作者]

蓝老师您好!去年6月七尾狐采菊东篱下向您探讨多栏式会计账问题,我也有同样的需求,认真拜读了您和采菊东篱下的讨论,高手讨论我似懂非懂,比葫芦画瓢我试了试,按36楼的代码

With Tables("多栏式明细账_Table1")

    For i As Integer = .Rows.count - 1 To 0 Step -1

        .Rows(i).Delete

    Next

    Dim g As Subtotalgroup

    .SubtotalGroups.Clear()

    Dim cmd As New SQLCommand

    Dim dt As DataTable

    cmd.CommandText = "Select 日期,字号,凭证号,摘要,二级科目,借方金额,贷方金额 fr om {财务数据源} Where [日期] >= #" & e.Form.Controls("StartDate").Value & "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "# And [会计科目] = '" & e.Form.Controls("combobox1").Value & "'"

    dt = cmd.ExecuteReader()

    Dim b As New CrossTableBuilder("统计表1",dt)

    b.HGroups.AddDef("日期",DateGroupEnum.Year,"")

    b.HGroups.AddDef("日期",DateGroupEnum.Month,"")

    b.HGroups.AddDef("日期",DateGroupEnum.Day,"")

    b.HGroups.AddDef("字号")

    b.HGroups.AddDef("凭证号")

    b.HGroups.AddDef("摘要")

    b.HGroups.AddDef("贷方金额")

    b.VGroups.AddDef("二级科目","{0}")

    b.Totals.AddDef("借方金额")

    'b.Filter = "[二级科目] Is Not Null"

    b.Build(True)

    .DataSource = b.BuildDataSource

    .Cols("借方金额_1").Visible = False

    Dim ckb As DataTable = e.Form.Controls("Table1").Table.DataTable

    If ckb.DataCols.Contains("借方金额") = False Then

        ckb.DataCols.add("借方金额", Gettype(Double))

    End If

    DataTables("多栏式明细账_Table1").DataCols("借方金额").SetFormat("#,###.##")

    .Cols("贷方金额").Move(.Cols.count - 1)

    For Each dr As DataRow In DataTables("多栏式明细账_Table1").DataRows

        Dim z As Double = 0

        For Each c As Col In .Cols

            Dim a As Double

            If c.name = "" OrElse c.name = "" OrElse c.name = "" OrElse c.name = "字号" OrElse c.name = "凭证号" OrElse c.name = "摘要" OrElse c.name = "借方金额" OrElse c.name = "贷方金额" Then

                e.Cancel = True

            Else

                a = iif(dr.IsNull(c.name),0,dr(c.name))

                z = z + a

            End If

        Next

        dr("借方金额") = z

    Next

    Dim s As String = ""

    For Each c As Col In .Cols

        If c.name = "" OrElse c.name = "" OrElse c.name = "" OrElse c.name = "字号" OrElse c.name = "凭证号" OrElse c.name = "摘要" Then

            e.Cancel = True

        Else

            s = s & "," & c.name

            DataTables("多栏式明细账_Table1").DataCols(c.name).SetFormat("#,###.##")

        End If

    Next

    g = New Subtotalgroup

    g.GroupOn = ""

    g.TotalOn = s.Trim(",")

    g.Caption = "本月发生额"

    .SubtotalGroups.Add(g)

    g = New Subtotalgroup

    g.GroupOn = ""

    g.Caption = "{0}"

    .SubtotalGroups.Add(g)

    .Sort = ","

    .Subtotal(True)

    Dim r As Row

    For i As Integer = 0 To .Rows.Count(True) - 1

        r = .Rows(i,True)

        If r.IsGroup AndAlso r("") Like "*" '如果是分组行

            Dim f As String = " = " & .Rows(i-2,True)("") & " And <= " & r("").trim("")

            r("") = "本年累计"

            For Each c As Col In .Cols

                If c.name = "" OrElse c.name = "" OrElse c.name = "" OrElse c.name = "字号" OrElse c.name = "凭证号" OrElse c.name = "摘要" Then

                    e.Cancel = True

                Else

                    r(c.name) = .Compute("Sum(" & c.name & ")",f)

                End If

            Next

        End If

    Next

    Dim sa As String = "|35||30||30|字号|35|凭证号|35|摘要|250|[其它]"

    Dim ct As WinForm.Table = e.Form.Controls("Table1")

    Dim t As Table = ct.Table

    Dim str As String

    Dim w As Integer = 0

    Dim w2 As Integer = (ct.Width - 35 * 3 - 30 * 2 - 250) / (t.cols.Count - 6)

    If w2 > 0 Then

        For Each c As Col In .Cols

            If c.name = "" OrElse c.name = "" OrElse c.name = "" OrElse c.name = "字号" OrElse c.name = "凭证号" OrElse c.name = "摘要" Then

                e.Cancel = True

            Else

                str = str & "|" & c.name & "|" & w2

            End If

        Next

        sa = sa.replace("[其它]",str.trim("|"))

        t.SetColVisibleWidth(sa)

    End If

    .AutoSizeRows()

End With

 

出现如下错误提示

.NET Framework 版本:4.0.30319.36543

Foxtable 版本:2022.1.30.2

错误所在事件:

详细错误信息:

语法错误 (操作符丢失) 在查询表达式 '贷方金额 fr om [~TMPCLP财务数据源] Where [日期] >= #2020-11-01# And [日期] <= #2022-01-31# And [会计科目] = '6602 管理费用'' 中。

敬请蓝老师指导修改一下,我非常感谢!


 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105473 积分:536350 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2022/5/16 17:26:00 [只看该作者]

去掉fr om中间的空格。

因为这个原因:http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&Id=69632,sql语句为了正常发布,会加空格,测试的时候自己去掉

 回到顶部
帅哥哟,离线,有人找我吗?
13315253800
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:二尾狐 帖子:534 积分:3874 威望:0 精华:0 注册:2018/1/3 16:03:00
  发帖心情 Post By:2022/5/16 20:37:00 [只看该作者]

好,我试试,谢谢蓝老师!

 回到顶部