- Foxtable(狐表) (http://www.foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://www.foxtable.com/bbs/list.asp?boardid=2)
---- [求助][已解决]一段菜单代码，如何提高效率和精简了 (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=50083)
-- 发布时间：2014/4/29 10:00:00
-- [求助][已解决]一段菜单代码，如何提高效率和精简了
Dim xq As RibbonMenu.ComboBox = RibbonTabs("内容查询").Groups("精确查询").Items("辖区查询")
Dim lx As RibbonMenu.ComboBox = RibbonTabs("内容查询").Groups("精确查询").Items("类型查询")
Dim zt As RibbonMenu.ComboBox = RibbonTabs("内容查询").Groups("精确查询").Items("状态查询")
Dim xq1 As String = xq.Text
Dim lx1 As String = lx.Text
Dim zt1 As String = zt.Text
Dim dt1 As Date = RibbonTabs("内容查询").Groups("按日期").Items("开始时间").Value
Dim dt2 As Date = RibbonTabs("内容查询").Groups("按日期").Items("终止时间").Value
Dim y1 As Integer = dt1.Year
Dim m1 As Integer = dt1.Month
Dim d1 As Integer = dt1.Day
Dim y2 As Integer = dt2.year
Dim m2 As Integer = dt2.Month
Dim d2 As Integer = dt2.Day
Dim time As String
If dt1 <> Nothing And dt2 <> Nothing Then
time = "签约时间 >= #" & y1 & "/" & m1 & "/" & d1 & "# And 签约时间 <= #" & y2 & "/" & m2 & "/" & d2 & "# "
Else If dt1 <> Nothing Then
time = "签约时间 >= #" & y1 & "/" & m1 & "/" & d1 & "# "
Else If dt2 <> Nothing Then
time = "签约时间 <= #" & y2 & "/" & m2 & "/" & d2 & "# "
Else time= "签约时间 <= #2099/01/01#"
End If
Dim qycx As New SQLJoinTableBuilder("综合查询", "客户资料") \'生成查询表
qycx.ConnectionName = "JD"
qycx.AddCols("合同编号","项目类型","区域","服务状态","签约名称","签约时间","地址","联系人","电话","业务员","备注")
qycx.Build
MainTable = Tables("综合查询")
Dim Filter As String
If xq1 IsNot "" Then
If lx1 IsNot "" Then
If zt1 IsNot "" Then
Filter = "区域 Like \'*" & xq1 & "*\' And 项目类型 Like \'*" & lx1 & "*\' And 服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Tables("综合查询").Filter = Filter
Else
Filter = "区域 Like \'*" & xq1 & "*\' And 项目类型 Like \'*" & lx1 & "*\' And " & time & " "
Tables("综合查询").Filter = Filter
End If
Else
If zt1 IsNot "" Then
Filter = "区域 Like \'*" & xq1 & "*\' And 服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Tables("综合查询").Filter = Filter
Else
Filter = "区域 Like \'*" & xq1 & "*\' and " & time & " "
Tables("综合查询").Filter = Filter
End If
End If
Else If lx1 IsNot "" Then
If zt1 IsNot "" Then
Filter = "项目类型 Like \'*" & lx1 & "*\' And 服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Tables("综合查询").Filter = Filter
Else
Filter = "项目类型 Like \'*" & lx1 & "*\' and " & time & " "
Tables("综合查询").Filter = Filter
End If
Else
If zt1 IsNot "" Then
Filter = "服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Tables("综合查询").Filter = Filter
Else
Tables("综合查询").Filter = time
End If
End If
这个代码是先生成表，后做筛选，降低了效率，要改变为 qycx.Filter = filter 又发现Filter代码出不来了。
另外感觉这个结构太乱，反复判断，有没有什么方法简单点
-- 发布时间：2014/4/29 10:09:00
--
效率难有什么大提示.
-- 发布时间：2014/4/29 10:18:00
--
呃，楼主 SQLJoinTableBuilder 不是也有 Filter 的么？注意，它使用 Like 的时候，要把 * 改成 %
-- 发布时间：2014/4/29 10:20:00
--
Dim xq As RibbonMenu.ComboBox = RibbonTabs("内容查询").Groups("精确查询").Items("辖区查询")
Dim lx As RibbonMenu.ComboBox = RibbonTabs("内容查询").Groups("精确查询").Items("类型查询")
Dim zt As RibbonMenu.ComboBox = RibbonTabs("内容查询").Groups("精确查询").Items("状态查询")
Dim xq1 As String = xq.Text
Dim lx1 As String = lx.Text
Dim zt1 As String = zt.Text
Dim dt1 As Date = RibbonTabs("内容查询").Groups("按日期").Items("开始时间").Value
Dim dt2 As Date = RibbonTabs("内容查询").Groups("按日期").Items("终止时间").Value
Dim y1 As Integer = dt1.Year
Dim m1 As Integer = dt1.Month
Dim d1 As Integer = dt1.Day
Dim y2 As Integer = dt2.year
Dim m2 As Integer = dt2.Month
Dim d2 As Integer = dt2.Day
Dim time As String
If dt1 <> Nothing And dt2 <> Nothing Then
time = "签约时间 >= #" & y1 & "/" & m1 & "/" & d1 & "# And 签约时间 <= #" & y2 & "/" & m2 & "/" & d2 & "# "
Else If dt1 <> Nothing Then
time = "签约时间 >= #" & y1 & "/" & m1 & "/" & d1 & "# "
Else If dt2 <> Nothing Then
time = "签约时间 <= #" & y2 & "/" & m2 & "/" & d2 & "# "
Else time= "签约时间 <= #2099/01/01#"
End If
Dim qycx As New SQLJoinTableBuilder("综合查询", "客户资料") \'生成查询表
qycx.ConnectionName = "JD"
qycx.AddCols("合同编号","项目类型","区域","服务状态","签约名称","签约时间","地址","联系人","电话","业务员","备注")
Dim Filter As String
If xq1 IsNot "" Then
If lx1 IsNot "" Then
If zt1 IsNot "" Then
Filter = "区域 Like \'*" & xq1 & "*\' And 项目类型 Like \'*" & lx1 & "*\' And 服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Else
Filter = "区域 Like \'*" & xq1 & "*\' And 项目类型 Like \'*" & lx1 & "*\' And " & time & " "
End If
Else
If zt1 IsNot "" Then
Filter = "区域 Like \'*" & xq1 & "*\' And 服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Else
Filter = "区域 Like \'*" & xq1 & "*\' and " & time & " "
End If
End If
Else If lx1 IsNot "" Then
If zt1 IsNot "" Then
Filter = "项目类型 Like \'*" & lx1 & "*\' And 服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Else
Filter = "项目类型 Like \'*" & lx1 & "*\' and " & time & " "
End If
Else
If zt1 IsNot "" Then
Filter = "服务状态 Like \'*" & zt1 & "*\' and " & time & " "
Else
Tables("综合查询").Filter = time
End If
End If
qycx.Filter = Filter
qycx.Build
MainTable = Tables("综合查询")
现在改成了这个样子，就过不去了，
此主题相关图片如下：捕获.png
-- 发布时间：2014/4/29 10:24:00
--
在一个，你filter的生成，也很有问题，可以写成类似这样吗？
Dim Filter As String = "1=1"
If xq1 > "" Then
Filter = Filter & " And 区域 like \'%" & xq1 & "%\'"
End If
If lx1 > "" Then
Filter = Filter & " And 项目类型 like \'%" & lx1 & "%\'"
End If
msgbox(Filter)
-- 发布时间：2014/4/29 10:39:00
--
回复4楼，请看3楼，*要改成%，如果是SQLServer数据库，#还要改成\'
-- 发布时间：2014/4/29 10:40:00
--
或者直接这样替换
Filter = Filter.Replace("*", "%").Replace("#", "\'")
-- 发布时间：2014/4/29 11:24:00
--
Dim xq As String = RibbonTabs("内容查询").Groups("精确查询").Items("辖区查询").Text
Dim lx As String = RibbonTabs("内容查询").Groups("精确查询").Items("类型查询").Text
Dim zt As String = RibbonTabs("内容查询").Groups("精确查询").Items("状态查询").Text
Dim dt1 As Date = RibbonTabs("内容查询").Groups("按日期").Items("开始时间").Value
Dim dt2 As Date = RibbonTabs("内容查询").Groups("按日期").Items("终止时间").Value
Dim time As String
If dt1 <> Nothing AndAlso dt2 <> Nothing Then
time = "签约时间 >= \'" & dt1 & "\' And 签约时间 <= \'" & dt2 & "\' "
Else If dt1 <> Nothing Then
time = "签约时间 >= \'" & dt1 & "\' "
Else If dt2 <> Nothing Then
time = "签约时间 <= \'" & dt2 & "\' "
Else time= "签约时间 <= \'2099/01/01\'"
End If
Dim qycx As New SQLJoinTableBuilder("综合查询", "客户资料") \'生成查询表
qycx.ConnectionName = "JD"
qycx.AddCols("合同编号","项目类型","区域","服务状态","签约名称","签约时间","地址","联系人","电话","业务员","备注")
Dim Filter As String = time
If xq > "" Then
Filter = Filter & " And 区域 like \'%" & xq & "%\'"
End If
If lx > "" Then
Filter = Filter & " And 项目类型 like \'%" & lx & "%\'"
End If
If zt > "" Then
Filter = Filter & " And 服务状态 like \'%" & zt & "%\'"
End If
qycx.Filter = Filter
qycx.Build
MainTable = Tables("综合查询")
这回舒服多了，多谢各位老师的指点。