查询问题 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

查询按钮的代码

Dim Filter As String
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        Filter = "品牌 like '*" & .Value & "*'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "销售型号 like'*" & .Value & "*'"
    End If
End With
With e.Form.Controls("combobox12")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "销售商场 like'*" & .Value & "*'"
    End If
End With
With e.Form.Controls("StartDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "销售日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "销售日期 <= #" & .Value & "#"
    End If
End With
If Filter > "" Then
    Tables("查询表").Filter = Filter
End If

统计查询的代码

Dim Filter As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "出库商场 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "型号 like'*" & .Value & "*'"

    End If
End With
Dim Filter1 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "退货商场 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "型号 like'*" & .Value & "*'"

End If
End With

Dim Filter3 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter3 > "" Then
            Filter3 = Filter3 & " And "
        End If
        Filter3 = Filter3 & "销售日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter3 > "" Then
            Filter3 = Filter3 & " And "
        End If
        Filter3 = Filter3 & "销售日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter3 > "" Then
            Filter3 = Filter3 & " And "
        End If
        Filter3 = Filter3 & "销售商场 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter3 > "" Then
            Filter3 = Filter3 & " And "
        End If
        Filter3 = Filter3 & "销售型号 like'*" & .Value & "*'"

End If
End With

Dim Filter4 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter4 > "" Then
            Filter4 = Filter4 & " And "
        End If
        Filter4 = Filter4 & "日期 < #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter4 > "" Then
            Filter4 = Filter4 & " And "
        End If
        Filter4 = Filter4 & "出库商场 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter4 > "" Then
            Filter4 = Filter4 & " And "
        End If
        Filter4 = Filter4 & "型号 like'*" & .Value & "*'"

End If
End With

Dim Filter5 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter5 > "" Then
            Filter5 = Filter5 & " And "
        End If
        Filter5 = Filter5 & "日期 < #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter5 > "" Then
            Filter5 = Filter5 & " And "
        End If
        Filter5 = Filter5 & "退货商场 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter5 > "" Then
            Filter5 = Filter5 & " And "
        End If
        Filter5 = Filter5 & "型号 like'*" & .Value & "*'"

    End If
End With
Dim Filter6 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter6 > "" Then
            Filter6 = Filter6 & " And "
        End If
        Filter6 = Filter6 & "销售日期 < #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter6 > "" Then
            Filter6 = Filter6 & " And "
        End If
        Filter6 = Filter6 & "销售商场 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter6 > "" Then
            Filter6 = Filter6 & " And "
        End If
        Filter6 = Filter6 & "销售型号 like'*" & .Value & "*'"

End If
End With

Dim bd1 As New SqlGroupTableBuilder("统计表1", "出库明细")
Dim dt1 As fxDataSource
bd1.Groups.AddDef("型号")'根据型号分组
bd1.Groups.AddDef("品牌")'根据型号分组
bd1.Groups.AddDef("出库商场")'根据型号分组
bd1.Groups.AddDef("单价")'根据型号分组
'bd1.Groups.AddDef("备注")'根据型号分组
bd1.Totals.AddDef("数量", "入库_数量")'对数量进行统计
bd1.Totals.AddDef("单价", "入库_金额")'对金额进行统计
bd1.Filter = Filter
bd1.SubtotalLevel = 1
dt1 = bd1.BuildDataSource()

Dim bd2 As New SqlGroupTableBuilder("统计表2", "退货明细")
Dim dt2 As fxDataSource
bd2.Groups.AddDef("型号")'根据型号分组
bd2.Totals.AddDef("数量", "退货_数量")'对数量进行统计
bd2.Totals.AddDef("单价", "退货_金额")'对金额进行统计
bd2.Filter = Filter1
dt2 = bd2.BuildDataSource()

Dim bd3 As New SqlGroupTableBuilder("统计表3", "销售资料")
Dim dt3 As fxDataSource
bd3.Groups.AddDef("销售型号")'根据型号分组
bd3.Totals.AddDef("数量", "销售_数量")'对数量进行统计
bd3.Totals.AddDef("销售单价", "销售_金额")'对金额进行统计
bd3.Filter = Filter3
dt3 = bd3.BuildDataSource()

Dim bd4 As New SqlGroupTableBuilder("统计表4", "出库明细")
Dim dt4 As fxDataSource
bd4.Groups.AddDef("型号")'根据型号分组
bd4.Totals.AddDef("数量", "出货期初_数量")'对数量进行统计
bd4.Totals.AddDef("单价", "出货期初_单价")'对金额进行统计
bd4.Filter = Filter4
dt4 = bd4.BuildDataSource()

Dim bd5 As New SqlGroupTableBuilder("统计表5", "退货明细")
Dim dt5 As fxDataSource
bd5.Groups.AddDef("型号")'根据型号分组
bd5.Totals.AddDef("数量", "退货期初_数量")'对数量进行统计
bd5.Totals.AddDef("单价", "退货期初_单价")'对金额进行统计
bd5.Filter = Filter5
dt5 = bd5.BuildDataSource()

Dim bd6 As New SqlGroupTableBuilder("统计表6", "销售资料")
Dim dt6 As fxDataSource
bd6.Groups.AddDef("销售型号")'根据型号分组
bd6.Totals.AddDef("数量", "销售期初_数量")'对数量进行统计
bd6.Totals.AddDef("销售单价", "销售期初_销售单价")'对金额进行统计
bd6.Filter = Filter6
dt6 = bd6.BuildDataSource()

dt1.Combine("型号", dt2, "型号")'将销售统计数据组合到出货统计数据
dt1.Combine("型号", dt3, "销售型号")
dt1.Combine("型号", dt4, "型号")
dt1.Combine("型号", dt5, "型号")
dt1.Combine("型号", dt6, "销售型号")

Tables("统计查询_Table1").DataSource = dt1 '将统计结果绑定到Table
With DataTables("统计查询_Table1").DataCols '用表达式列计算库存数据
    .Add("期初_数量", GetType(Integer), "IsNull([出货期初_数量], 0) - IsNull([销售期初_数量], 0) - IsNull([退货期初_数量], 0)")
    .Add("期初_金额", GetType(Double), "IsNull([出货期初_数量], 0) * IsNull([单价], 0)")
    .Add("库存_数量", GetType(Integer), "IsNull([期初_数量], 0) + IsNull([入库_数量], 0) - IsNull([退货_数量], 0)-IsNull([销售_数量], 0)")
    .Add("库存_金额", GetType(Double), "IsNull([库存_数量], 0) * IsNull([单价], 0)")

End With

Dim g As SubtotalGroup '定义一个新的分组
    g = New SubtotalGroup
    g.GroupOn = "出库商场" '分组列为客户
    g.TotalOn = "期初_数量,期初_金额,入库_数量,入库_金额,退货_数量,退货_金额,销售_数量,销售_金额,库存_数量,库存_金额" '对数量和金额进行统计
    g = New SubtotalGroup
    g.GroupOn = "*" '分组列为客户
    g.TotalOn = "期初_数量,期初_金额,入库_数量,入库_金额,退货_数量,退货_金额,销售_数量,销售_金额,库存_数量,库存_金额" '对数量和金额进行统计
With Tables("统计查询_Table1")
     .SubtotalGroups.Clear() '清除原有的分组
     .SubtotalGroups.Add(g) '加入刚刚定义的分组
     .Subtotal() '生成汇总模式
End With
'Tables("统计查询_Table1").Cols("备注").Move(20)
Tables("统计查询_Table1").Cols("期初_数量").Move(4)
Tables("统计查询_Table1").Cols("期初_金额").Move(5)
Tables("统计查询_Table1").cols("型号").width = 120
Tables("统计查询_Table1").cols("出库商场").width = 150
Tables("统计查询_Table1").cols("入库_数量").width = 40
Tables("统计查询_Table1").cols("退货_数量").width = 40
Tables("统计查询_Table1").cols("销售_数量").width = 40
Tables("统计查询_Table1").Cols("出货期初_数量").Visible = False
Tables("统计查询_Table1").Cols("出货期初_单价").Visible = False
Tables("统计查询_Table1").Cols("退货期初_数量").Visible = False
Tables("统计查询_Table1").Cols("退货期初_单价").Visible = False
Tables("统计查询_Table1").Cols("销售期初_数量").Visible = False
Tables("统计查询_Table1").Cols("销售期初_销售单价").Visible = False
Tables("统计查询_Table1").ExtendLastCol = True

查询和统计用法不一样吗，，查询按钮会模糊查找，，统计里面就没有效果，麻烦老师给指点一下

此主题相关图片如下：1715820915504.png

此主题相关图片如下：1715820948536.png

此主题相关图片如下：1715820984464.png

主题：查询问题