专家坐堂-筛选一个时间段到统计表1Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  筛选一个时间段到统计表1  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=108645)

--  作者：LTHERO
--  发布时间：2017/10/26 14:32:00
--  筛选一个时间段到统计表1
筛选时间段到统计表中，然后在通过统计表生成图表！

筛选自定义XY轴文本如下：

Dim Filter As String

Dim a = e.Form.Controls("TextBox1").Text
Dim b = e.Form.Controls("TextBox2").Text

With e.Form.Controls("TextBox1")
If .value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "[" & a & "] Like \'%" & .text & "%\'"
End If
End With

With e.Form.Controls("TextBox2")
If .value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "[" & b & "] Like \'%" & .text & "%\'"
End If
End With

With e.Form.Controls("DateTimePicker1")
If .value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "记录填写时间 >= \'#" & .text & "#\'"
End If
End With

With e.Form.Controls("DateTimePicker2")
If .value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "记录填写时间 <= \'#" & .text & "#\'"
End If
End With

If Filter > "" Then
Tables("工时").Filter = Filter
End If

Dim t As Table = Tables("工时")
Dim g As Subtotalgroup
t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
\'g.GroupOn = "姓名"
g.Caption = "{0}"
t.SubtotalGroups.Add(g)
t.Subtotal()

Dim r As Row
For i As Integer = 0 To t.Rows.Count(True) - 1
r = t.Rows(i,True)
If r.IsGroup \'如果是分组行
Dim f As String = "[" & a & "] = \'" & r("[" & a & "]") & "\'"
Dim v As String = " [" & b & "]:" & t.Compute("Sum([" & b & "])",f)
r("[" & a & "]") = "[" & a & "]:" & r("[" & a & "]") & v
End If
Next

Dim b1 As New GroupTableBuilder("统计表1",DataTables("工时"))
b1.Groups.AddDef("[" & a & "]","" & a & "") \'添加日期列用于分组,并用"月"代替原名称
b1.Totals.AddDef("[" & b & "]") \'添加数量列用于统计
b1.Subtotal = True \'生成汇总模式
b1.Build \'生成统计表
MainTable = Tables("统计表1") \'打开生成的统计表

请帮我加上筛选时间段的代码！试了很久写不出来谢谢！

注：记录填写时间只有一列！！

--  作者：LTHERO
--  发布时间：2017/10/26 14:33:00
--
这是工时表
此主题相关图片如下：工时.png

--  作者：LTHERO
--  发布时间：2017/10/26 14:33:00
--
这是柱图页面！
此主题相关图片如下：柱图.png

--  作者：LTHERO
--  发布时间：2017/10/26 14:34:00
--
就是先筛选数据到统计表1中，然后再生成图表！

--  作者：有点甜
--  发布时间：2017/10/26 14:42:00
--

Dim Filter As String

Dim a = e.Form.Controls("TextBox1").Text

Dim b = e.Form.Controls("TextBox2").Text

With e.Form.Controls("TextBox1")

If .value IsNot Nothing Then

If Filter >"" Then

Filter = Filter & " And "

End If

Filter = Filter & "[" & a & "] Like \'%" & .text & "%\'"

End If

End With

With e.Form.Controls("TextBox2")

If .value IsNot Nothing Then

If Filter >"" Then

Filter = Filter & " And "

End If

Filter = Filter & "[" & b & "] Like \'%" & .text & "%\'"

End If

End With

With e.Form.Controls("DateTimePicker1")

If .value IsNot Nothing Then

If Filter >"" Then

Filter = Filter & " And "

End If

Filter = Filter & "记录填写时间 >= #" & .text & "#"

End If

End With

With e.Form.Controls("DateTimePicker2")

If .value IsNot Nothing Then

If Filter >"" Then

Filter = Filter & " And "

End If

Filter = Filter & "记录填写时间 <= #" & .text & "#"

End If

End With

If Filter > "" Then

Tables("工时").Filter = Filter

End If

Dim t As Table = Tables("工时")

Dim g As Subtotalgroup

t.SubtotalGroups.Clear()

t.GroupAboveData = False

t.TreeVisible = False

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

\'g.GroupOn = "姓名"

g.Caption = "{0}"

t.SubtotalGroups.Add(g)

t.Subtotal()

Dim r As Row

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

r = t.Rows(i,True)

If r.IsGroup \'如果是分组行

Dim f As String = "[" & a & "] = \'" & r("[" & a & "]") & "\'"

Dim v As String = " [" & b & "]:" & t.Compute("Sum([" & b & "])",f)

r("[" & a & "]") = "[" & a & "]:" & r("[" & a & "]") & v

End If

Next

Dim b1 As New GroupTableBuilder("统计表1",DataTables("工时"))

b1.Groups.AddDef("[" & a & "]","" & a & "") \'添加日期列用于分组,并用"月"代替原名称

b1.Totals.AddDef("[" & b & "]") \'添加数量列用于统计

b1.Subtotal = True \'生成汇总模式

b1.Filter = Filter

b1.Build \'生成统计表

MainTable = Tables("统计表1") \'打开生成的统计表

--  作者：LTHERO
--  发布时间：2017/10/26 16:00:00
--
出现这种错误难道我需要把Double类型改为字符类型才可以？

--  作者：LTHERO
--  发布时间：2017/10/26 16:00:00
--
图片
此主题相关图片如下：错误.png

--  作者：有点甜
--  发布时间：2017/10/26 16:07:00
--

修改

With e.Form.Controls("TextBox1")

If .value IsNot Nothing Then

If Filter >"" Then

Filter = Filter & " And "

End If

Filter = Filter & "convert(" & a & ",\'System.String\') Like \'%" & .text & "%\'"

End If

End With

With e.Form.Controls("TextBox2")

If .value IsNot Nothing Then

If Filter >"" Then

Filter = Filter & " And "

End If

Filter = Filter & "convert(" & b & ",\'System.String\') Like \'%" & .text & "%\'"

End If

End With

--  作者：LTHERO
--  发布时间：2017/10/26 16:11:00
--
这是什么问题呢！
此主题相关图片如下：错误1.png

--  作者：LTHERO
--  发布时间：2017/10/26 16:15:00
--
System.DateTime
是这样用吗
With e.Form.Controls("DateTimePicker1")
If .value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "convert("工作日期",\'System.DateTime\') Like \'%" & .text & "%\'"
End If
End With