Foxtable(狐表) → 用户栏目 → 专家坐堂 → 时段过滤条件语句问题


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

主题：时段过滤条件语句问题
帅哥哟，离线，有人找我吗？
utcxray
 小大  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：三尾狐 帖子：668 积分：5053 威望：0 精华：0 注册：2011/10/22 13:37:00 		时段过滤条件语句问题  发帖心情 Post By：2014/9/15 17:25:00 [只看该作者]

想定义时段过滤条件：

当年

t.Filter = " > = #" & Date.Now().Year & "-01-01" & "# And " < = #" & Date.Now() & "#"

说明：

‘此代码取出系统当前日期所属年份，从1月1日起开始计时。

  Date.Now().Year & "-01-01" 

’取当前日期

Date.Now() 


当月

t.Filter = & " > = #" & Format(Date.Now(),"y") & "# And "< = #" & Date.Now() & "#"


实际执行过程中出错，请高手指点
 回到顶部
帅哥哟，离线，有人找我吗？
有点甜
 小大  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：版主 帖子：85326 积分：427815 威望：0 精华：5 注册：2012/10/18 22:13:00 		  发帖心情 Post By：2014/9/15 17:30:00 [只看该作者]

 当年

 

Dim d As Date = new Date(Date.Today.Year, 1, 1)
t.Filter = "日期 >= #" & d & "# And 日期 < #" & d.AddYears(1) & "#"
 回到顶部
帅哥哟，离线，有人找我吗？
有点甜
 小大  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：版主 帖子：85326 积分：427815 威望：0 精华：5 注册：2012/10/18 22:13:00 		  发帖心情 Post By：2014/9/15 17:31:00 [只看该作者]

当月

 

Dim d As Date = new Date(Date.Today.Year, Date.Today.Month, 1)
t.Filter = "日期 >= #" & d & "# And 日期 < #" & d.addMonths(1) & "#"
 回到顶部
帅哥哟，离线，有人找我吗？
狐狸爸爸
 小大  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：管理员 帖子：47448 积分：251060 威望：0 精华：91 注册：2008/6/17 17:14:00 		  发帖心情 Post By：2014/9/15 17:37:00 [只看该作者]


 回到顶部
帅哥哟，离线，有人找我吗？
lyfxybc
 小大  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：六尾狐 帖子：1290 积分：9820 威望：0 精华：0 注册：2012/9/19 21:13:00 		  发帖心情 Post By：2014/9/15 17:46:00 [只看该作者]

 

按时段加载数据

我们经常需要加载某个时段的数据，下面列出了各种情况的加载代码给大家参考。

加载指定年份数据

例如加载2011年的订单：

Dim y As Integer = 2011 '指定年份
Dim dt1 As New Date(y, 1, 1)
Dim dt2 As New Date(y, 12, 31)
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

加载指定月份数据

例如加载2011年6月份的订单：

Dim y As Integer = 2011 '指定年份
Dim m As Integer = 6 '指定月份
Dim dt1 As New Date(y, m, 1)
Dim dt2 As New Date(y, m, Date.DaysInMonth(2011,6)) '获取该月的最后一天
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

加载指定季度的数据

例如加载2011年2季度的订单：

Dim y As Integer = 2011 '指定年份
Dim q As Integer = 2 '指定季度
Dim dt1 As New Date(y, 3 * (q - 1) + 1, 1) '获取该季度的第一天
Dim dt2 As New Date(y, 3 * q, Date.DaysInMonth(2011,3 * q)) '获取该季度的最后一天
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

加载本年数据

例如加载本年的订单：

Dim y As Integer = Date.Today.Year
Dim dt1 As New Date(y, 1, 1)
Dim dt2 As New Date(y, 12, 31)
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

加载本季度数据

例如加载本季度的订单：

Dim y As Integer = Date.Today.Year
Dim q As Integer = (Date.Today.Month - 1) \ 3 + 1 '计算现在是第几个季度
Dim dt1 As New Date(y, 3 * (q - 1) + 1, 1) '获取本季度的第一天
Dim dt2 As New Date(y, 3 * q, Date.DaysInMonth(2011,3 * q)) '获取本季度的最后一天
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

注意第二行代码，用的是整除运算符（\），而不是通常的除法运算符（/），整除运算符会直接对结果取整。

加载本月数据

例如加载本月的订单：

Dim y As Integer = Date.Today.Year
Dim m As Integer = Date.Today.Month
Dim dt1 As New Date(y, m, 1)
Dim dt2 As New Date(y, m, Date.DaysInMonth(y, m)) '获取本月的最后一天
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

加载本周数据

例如加载本周的订单：

Dim y As Integer = Date.Today.Year
Dim w As Integer = Date.Today.DayOfWeek '算出今天是星期几
Dim dt1 As Date = Date.Today.AddDays(-w) '获取本周的第一天
Dim dt2 As Date = Date.Today.AddDays(6 - w) '获取本周的最后一天
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

注意事项

1、前面的代码全部是以内部表或Access数据源作为数据源的，如果你使用SQL Server作为数据源，那么日期不是用符号#括起来，而是用单引号括起来，所以下面这样代码：

Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

应该改为：

Filter = "日期 >= '" & dt1 & "' And 日期 <= '" & dt2 & "'"

2、这一节虽然以加载数据为例，但有关知识同样可以用于筛选，只需将最好两行代码：

DataTables("订单").LoadFilter = Filter
DataTables("订单").Load()

改为：

Tables("订单").Filter = Filter

即可用于筛选数据。

例如筛选本周订单：

Dim y As Integer = Date.Today.Year
Dim w As Integer = Date.Today.DayOfWeek '算出今天是星期几
Dim dt1 As Date = Date.Today.AddDays(-w) '获取本周的第一天
Dim dt2 As Date = Date.Today.AddDays(6 - w) '获取本周的最后一天
Dim Filter As String
Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
Tables("订单").Filter = Filter

 

l         本日

Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

Dim dt1 As New Date(y, m, d) '获取本月的最后一天

Dim dt2 As New Date(y, m, d) '获取本月的最后一天

Dim Filter As String

  Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

CurrentTable.Filter = Filter



昨日


Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

'For Each dr As DataRow In DataTables("月份起始设置").DataRows  

        If d>=2

           Dim dt1 As New Date(y, m, d-1) '获取本月的最后一天

           Dim dt2 As New Date(y, m, d-1) '获取本月的最后一天

           Dim Filter As String

           Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

           CurrentTable.Filter = Filter

        ElseIf d=1 And m>=2 Then

           Dim dt1 As New Date(y, m-1, Date.DaysInMonth(y, m-1)) '获取本月的最后一天

           Dim dt2 As New Date(y, m-1, Date.DaysInMonth(y, m-1)) '获取本月的最后一天

           Dim Filter As String

           Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

           CurrentTable.Filter = Filter

        ElseIf d=1 And m=1 Then

           Dim dt1 As New Date(y-1, m+11, Date.DaysInMonth(y-1, m+11)) '获取本月的最后一天

           Dim dt2 As New Date(y-1, m+11, Date.DaysInMonth(y-1, m+11)) '获取本月的最后一天 

           Dim Filter As String

           Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

           CurrentTable.Filter = Filter

        End If

'Next


本月


Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

Dim dt1 As New Date(y, m, 1)

Dim dt2 As New Date(y, m, Date.DaysInMonth(y, m)) '获取本月的最后一天

Dim Filter As String

    Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

CurrentTable.Filter = Filter


上月


Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

If m>=2

   Dim dt1 As New Date(y, m-1, 1)

   Dim dt2 As New Date(y, m-1, Date.DaysInMonth(y, m-1)) '获取本月的最后一天

   Dim Filter As String

    Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

   CurrentTable.Filter = Filter

ElseIf m=1 Then

    Dim dt1 As New Date(y, m-1, 1)

    Dim dt2 As New Date(y-1, m+11, Date.DaysInMonth(y-1, m+11)) '获取本月的最后一天

    Dim Filter As String

    Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

    CurrentTable.Filter = Filter

End If



本周


Dim y As Integer = Date.Today.Year

    Dim w As Integer = Date.Today.DayOfWeek

    Dim dt1 As Date = Date.Today.AddDays(-w)

    Dim dt2 As Date = Date.Today.AddDays(6 - w)

   Dim Filter As String

    Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

   CurrentTable.Filter = Filter



上周


Dim y As Integer = Date.Today.Year

Dim w As Integer = Date.Today.DayOfWeek '算出今天是星期几

Dim dt1 As Date = Date.Today.AddDays(-w-7) '获取本周的第一天

Dim dt2 As Date = Date.Today.AddDays(6 - w-7) '获取本周的最后一天

Dim Filter As String

   Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

CurrentTable.Filter = Filter




本季


Dim y As Integer = Date.Today.Year

Dim q As Integer = (Date.Today.Month - 1) \ 3 + 1

Dim t As Table = CurrentTable

    Dim dt1 As New Date(y, 3 * (q - 1) + 1, 1)

    Dim dt2 As New Date(y, 3 * q, Date.DaysInMonth(y,3 * q))

   Dim Filter As String

    Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

   CurrentTable.Filter = Filter


上季

Dim y As Integer = Date.Today.Year

Dim q As Integer = (Date.Today.Month - 1) \ 3 + 1 '计算现在是第几个季度

    If q>=2

       Dim dt1 As New Date(y, 3 * ((q-1) - 1) + 1, 1) '获取本季度的第一天

       Dim dt2 As New Date(y, 3 * (q-1), Date.DaysInMonth(y,3 * (q-1))) '获取本季度的最后一天

   Dim Filter As String

    Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

   CurrentTable.Filter = Filter

    ElseIf  q=1

       Dim dt1 As New Date(y-1, 3 * (q+3 - 1) + 1, 1) '获取本季度的第一天

       Dim dt2 As New Date(y-1, 3 * (q+3), Date.DaysInMonth(y-1,3 * (q+3))) '获取本季度的最后一天

   Dim Filter As String

    Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

   CurrentTable.Filter = Filter

End If



今年


Dim y As Integer = Date.Today.Year

Dim dt1 As New Date(y, 1, 1)

Dim dt2 As New Date(y, 12, 31)

Dim Filter As String

Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

CurrentTable.Filter = Filter



去年

Dim y As Integer = Date.Today.Year

Dim dt1 As New Date(y-1, 1, 1)

Dim dt2 As New Date(y-1, 12, 31)

Dim Filter As String

Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

CurrentTable.Filter = Filter
 回到顶部
帅哥哟，离线，有人找我吗？
lyfxybc
 小大  6楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：六尾狐 帖子：1290 积分：9820 威望：0 精华：0 注册：2012/9/19 21:13:00 		  发帖心情 Post By：2014/9/15 17:46:00 [只看该作者]

自定义时间

 

 
图片点击可在新窗口打开查看此主题相关图片如下：image001.jpg
图片点击可在新窗口打开查看

l       按设定日期计算年月季日

 

For Each dr As DataRow In DataTables("月份起始日设置").DataRows  

    Dim qs As Integer = dr("每月第一天")

    Dim js As Integer = dr("每月最后一天")

If e.DataCol.Name = "日期" Then

    If e.DataRow.IsNull("日期") Then

        e.DataRow("年") = Nothing

        e.DataRow("月") = Nothing

        e.DataRow("季") = Nothing

        e.DataRow("日") = Nothing

    Else

        Dim d As Date = e.NewValue

        If d.Month = 12 And d.day >= qs Then

            e.DataRow("年") = d.Year +1

        Else

           e.DataRow("年") = d.Year

        End If

        If d.Day < qs Then

            e.DataRow("月") = d.Month

        Else If d.Month = 11 Then

            e.DataRow("月") = 12

        Else

            e.DataRow("月") = (d.Month + 1) Mod 12

        End If

        If d.day > js Then

            e.DataRow("季") = DatePart("q",d.AddDays(10))

        Else

            e.DataRow("季") = DatePart("q",d)

        End If

        e.DataRow("日") = d.Day

End If

End If

Next

 

 

本月


Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

For Each dr As DataRow In DataTables("月份起始日设置").DataRows          

     Dim qs As Integer = dr("每月第一天")

     Dim js As Integer = dr("每月最后一天")

          If m>=1 And d>=qs

             Dim dt1 As New Date(y, m, qs)

             Dim dt2 As New Date(y, m+1, js) '获取本月的最后一天

             Dim Filter As String

             Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

             CurrentTable.Filter = Filter

          ElseIf m=1 And d<qs

              Dim dt1 As New Date(y-1, m+11, qs)

              Dim dt2 As New Date(y, m, js) '获取本月的最后一天

              Dim Filter As String

              Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

              CurrentTable.Filter = Filter

           End If

Next


上月


Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

For Each dr As DataRow In DataTables("月份起始日设置").DataRows          

     Dim qs As Integer = dr("每月第一天")

     Dim js As Integer = dr("每月最后一天")

          If m>2  And d>=qs

             Dim dt1 As New Date(y, m-2, qs)

             Dim dt2 As New Date(y, m-1, js) '获取本月的最后一天

             Dim Filter As String

             Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

             CurrentTable.Filter = Filter

          ElseIf m=2 And d>=qs

              Dim dt1 As New Date(y-1, m+11, qs)

              Dim dt2 As New Date(y, m-1, js) '获取本月的最后一天

              Dim Filter As String

              Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

              CurrentTable.Filter = Filter

          ElseIf m=1 And d<qs

              Dim dt1 As New Date(y-1, m+10, qs)

              Dim dt2 As New Date(y-1, m+11, js) '获取本月的最后一天

              Dim Filter As String

              Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

              CurrentTable.Filter = Filter

          End If        

Next



今年


Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

For Each dr As DataRow In DataTables("月份起始日设置").DataRows          

     Dim qs As Integer = dr("每月第一天")

     Dim js As Integer = dr("每月最后一天")

 

           If m>=1 And m<11

             Dim dt1 As New Date(y-1, 11, qs)

             Dim dt2 As New Date(y, m, d) '获取本月的最后一天

             Dim Filter As String

             Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

             CurrentTable.Filter = Filter

           ElseIf m=11  

              Dim dt1 As New Date(y-1, 11, qs)

              Dim dt2 As New Date(y, m, d) '获取本月的最后一天

             Dim Filter As String

             Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

             CurrentTable.Filter = Filter

            

           ElseIf m=12  And d<qs

              Dim dt1 As New Date(y-1, 11, qs)

              Dim dt2 As New Date(y, m, d) '获取本月的最后一天

             Dim Filter As String

             Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

             CurrentTable.Filter = Filter

             

          ElseIf m=12  And d>=qs

              Dim dt1 As New Date(y, 11, qs)

              Dim dt2 As New Date(y, m, d) '获取本月的最后一天

             Dim Filter As String

             Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

             CurrentTable.Filter = Filter

          End If          

 Next


去年

Dim y As Integer = Date.Today.Year

Dim m As Integer = Date.Today.Month

Dim d As Integer = Date.Today.day

For Each dr As DataRow In DataTables("月份起始日设置").DataRows          

     Dim qs As Integer = dr("每月第一天")

     Dim js As Integer = dr("每月最后一天")

     Dim dt1 As New Date(y-2, 12, qs)

     Dim dt2 As New Date(y-1, 12, js) '获取本月的最后一天

     Dim Filter As String

     Filter = "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"

     CurrentTable.Filter = Filter

Next
 回到顶部
帅哥哟，离线，有人找我吗？
utcxray
 小大  7楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：三尾狐 帖子：668 积分：5053 威望：0 精华：0 注册：2011/10/22 13:37:00 		  发帖心情 Post By：2014/9/15 17:49:00 [只看该作者]

Dim Chart As WinForm.Chart '定义一个图表变量
Dim Series As WinForm.ChartSeries '定义一个图系变量
Dim tbl As DataTable = DataTables("出库") '定义一个变量tbl引用数据表
Dim lst As List(Of String) = tbl.GetValues("出库日期> = " & Date.Now().Year & "-01-01" & "And  < = " & Date.Now() & " ") '获得日期名称集合
Chart = e.Form.Controls("Chart2") ' 引用窗口中的图表
Chart.SeriesList.Clear() '清除图表原来的图系
Series = Chart.SeriesList.Add() '增加一个图系
Series.Length = lst.Count '设置图系的长度
For i As Integer = 0 To lst.Count - 1 '指定每个数据点的位置
    Series.X(i) = i '指定水平坐标
    Series.Y(i) = tbl.Compute("Sum(金额)","[出库日期] = '" & lst(i) & " ' ") '指定垂直坐标
    Chart.AxisX.DateType = True 'X轴是日期型
    Chart.AxisX.SetValueLabel(i, Month(lst(i))) '指定字符表示
Next
Chart.AxisX.AnnoWithLabels = True '启用字符标示
Chart.SeriesList(0).LineColor= Color.Red
Series.MarkColor = Color.Green

请帮我改下粗体字部分，按当年改，谢谢
 回到顶部
帅哥哟，离线，有人找我吗？
utcxray
 小大  8楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：三尾狐 帖子：668 积分：5053 威望：0 精华：0 注册：2011/10/22 13:37:00 		  发帖心情 Post By：2014/9/15 17:52:00 [只看该作者]

谢谢狐狸爸爸，谢谢有点甜，谢谢lyfxybc

 回到顶部
RSS2.0 | Xhtml无图版 | Xslt无图版 Copyright © 2000 - 2018 foxtable.com Tel: 4000-810-820 粤ICP备11091905号
   Powered By Dvbbs Version 8.3.0
页面执行时间 0.05078 秒, 4 次数据查询