按时段加载数据
我们经常需要加载某个时段的数据,下面列出了各种情况的加载代码给大家参考。
加载指定年份数据
例如加载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(y,m))
'获取该月的最后一天
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(y,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(y,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