按时段筛选数据

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

筛选指定年份数据

例如筛选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 & "#"
Tables
("订单").Filter = Filter

筛选指定月份数据

例如筛选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(y,m)) '获取该月的最后一天
Dim
Filter As String
Filter
= "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
Tables
("订单").Filter = Filter

筛选指定季度的数据

例如筛选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(y,3 * q)) '获取该季度的最后一天
Dim
Filter As String
Filter
= "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
Tables
("订单").Filter = Filter

筛选本年数据

例如筛选本年的订单:

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 & "#"
Tables(
"订单").Filter = Filter

筛选本季度数据

例如筛选本季度的订单:

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(y,3 * q)) '获取本季度的最后一天
Dim
Filter As String
Filter
= "日期 >= #" & dt1 & "# And 日期 <= #" & dt2 & "#"
Tables(
"订单").Filter = Filter

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

筛选本月数据

例如筛选本月的订单:

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 & "#"
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


本页地址:http://www.foxtable.com/webhelp/topics/2720.htm