查询2月份的所有信息:select * from 表 where datepart(mm,日期)='02'
用Timer控件来判断是否已经到了下个月:Dim dtOld As Date Private Sub Form_Load() dtOld = Date Timer1.Interval = 2000 Timer1.Enabled = True End SubPrivate Sub Timer1_Timer() If Format(DatePart("m", dtOld), "00") <> Format(DatePart("m", Date), "00") Then '已经到了下个月,在这里调用更新函数,进行更新显示 End If dtOld = Date End Sub
触发查询的时候用变量记录当前月份: Dim d Dim Sql d = Format(Date, "M") ...... sql = "select * from tb where format(日期字段,'M')='"& d &"'"
哦,不要用DataEnvironment。引用ADO来做
更新函数这样写: ------------------------------ 引用ADO(工程/引用/Microsoft ActiveX Data Object 2.X Library)Private Sub RefreshDataGrid() Dim cn As New ADODB.Connection, rs As New ADODB.Recordset '打开Access数据库的连接,具体的需要改一下 cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MF.mdb;Persist Security Info=False" cn.CursorLocation = adUseClient rs.Open "select * from 支出表 where datepart('m',日期)='" & Format(Date, "m") & "'", cn, adOpenKeyset, adLockOptimistic Set DataGrid1.DataSource = rs End Sub
用DataEnvironment也可以 Private Sub RefreshDataGrid() '打开Access数据库的连接,具体的需要改一下 With DataEnvironment1 If .Connection1.State=1 Then .Connection1.Close If .rsCommand1.State=1 Then .rsCommand1.Close .Connection1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MF.mdb;Persist Security Info=False" .Connection1.CursorLocation = adUseClient .rsCommand1.Open "select * from 支出表 where datepart('m',日期)='" & Format(Date, "m") & "'", cn, adOpenKeyset, adLockOptimistic Set DataGrid1.DataSource = .rsCommand1 End With End Sub
简单点的就直接给个文本框给用户输入起止日期然后动态构造sql查询语句。
动态构造sql查询语句? ================================ 就是类似上面 faysky2() 兄的方法: rs.Open "select * from 支出表 where datepart('m',日期)='" & Format(Date, "m") & "'", cn, adOpenKeyset, adLockOptimistic把用户输入的条件作为你的查询语句的条件。。
.open "select sum(金额) from zhchu where format(日期,'yyyy') + format(日期,'m')=" & Format(DTPicker1.Value, "yyyym") & "+"Format(DTPicker2.Value, "yyyym") &"'"DTPicker1确定起始日期,DTPicker2确定截止日期就是根据以上确定的时间段计算出这个时间段的支出总额如何把结果显示在text控件
DTPicker1确定起始日期,DTPicker2确定截止日期 -------------------------------------------------------- .Open "select sum(金额) from zhchu where format(日期,'yyyym') >='" & Format(DTPicker1.Value, "yyyym") & "' and format(日期,'yyyym')<= '" & Format(DTPicker2.Value, "yyyym") & "'"
问题1: Private Sub Command3_Click() Text3.Text = "" On Error GoTo handleError With DataEnvironment1.rsCommand3 .Open "select sum(金额) from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'" Text3.Text = ![expr1000] & "" End With Form_Load_exit: Exit Sub handleError: On Error GoTo 0只能查询一次,再次查询没有反映。要关闭窗体,再次打开窗体才能再次查询。========================================= 问题2 Dim cs As New ADODB.Connection, kj As New ADODB.Recordset '支出情况
If SSTab1.Tab = 0 Then cs.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Persist Security Info=False;data source=" & App.Path & "\MF.mdb;Mode=ReadWrite" cs.CursorLocation = adUseClient kj.Open "select sum(金额) from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'", cs, adOpenKeyset, adLockOptimistic Text3.Text = ![expr1000] & "" End If中的Text3.Text = ![expr1000] & ""错误,怎么修改
问题1:打开记录前,先判断当前记录状态,如果已经打开,先关闭掉,然后再打开: ...... With DataEnvironment1.rsCommand3 If .State<>adStateClosed Then .Close ' 先关闭记录集 .Open "select sum(金额) from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'" ......问题2:给 sum(金额) 起个别名,用别名来取字段值:kj.Open "select sum(金额) As 总金额 from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'", cs, adOpenKeyset, adLockOptimistic Text3.Text = kj![总金额] & "" ' 注意看=后面的写法
Private Sub Form_Load()
dtOld = Date
Timer1.Interval = 2000
Timer1.Enabled = True
End SubPrivate Sub Timer1_Timer()
If Format(DatePart("m", dtOld), "00") <> Format(DatePart("m", Date), "00") Then
'已经到了下个月,在这里调用更新函数,进行更新显示
End If
dtOld = Date
End Sub
Dim d
Dim Sql
d = Format(Date, "M")
......
sql = "select * from tb where format(日期字段,'M')='"& d &"'"
------------------------------
引用ADO(工程/引用/Microsoft ActiveX Data Object 2.X Library)Private Sub RefreshDataGrid()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
'打开Access数据库的连接,具体的需要改一下 cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MF.mdb;Persist Security Info=False"
cn.CursorLocation = adUseClient rs.Open "select * from 支出表 where datepart('m',日期)='" & Format(Date, "m") & "'", cn, adOpenKeyset, adLockOptimistic Set DataGrid1.DataSource = rs
End Sub
Private Sub RefreshDataGrid()
'打开Access数据库的连接,具体的需要改一下
With DataEnvironment1
If .Connection1.State=1 Then .Connection1.Close
If .rsCommand1.State=1 Then .rsCommand1.Close
.Connection1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MF.mdb;Persist Security Info=False"
.Connection1.CursorLocation = adUseClient .rsCommand1.Open "select * from 支出表 where datepart('m',日期)='" & Format(Date, "m") & "'", cn, adOpenKeyset, adLockOptimistic Set DataGrid1.DataSource = .rsCommand1
End With
End Sub
================================
就是类似上面 faysky2() 兄的方法: rs.Open "select * from 支出表 where datepart('m',日期)='" & Format(Date, "m") & "'", cn, adOpenKeyset, adLockOptimistic把用户输入的条件作为你的查询语句的条件。。
起止日期是用DTPicker控件限定
如何实现?如何对2两个条件同时查询?
起止日期是用DTPicker控件限定
如何实现?如何对2两个条件同时查询?
=========================================
举一反三,既然查询的月份可以让用户设定,那么设定年份还不是一样的原理。
说到底就是拼凑出那句sql查询语句
简单的字符串操作。。
=============================================================================
运行时出现语法错误(操作符丢失)?还有DTPicker控件有两个,分别是起始时间和截止时间where datepart('yyyy',日期)+datepart('m',日期)='" & Format(Date, "yyyym") & "'"
哪错了?
convert是SQL表达式
Access库用Format函数来做:.Open "select * from table1 where format(日期,'yyyy') + format(日期,'m')='" & Format(Date, "yyyym") & "'"
对不对
--------------------------------------------------------
.Open "select sum(金额) from zhchu where format(日期,'yyyym') >='" & Format(DTPicker1.Value, "yyyym") & "' and format(日期,'yyyym')<= '" & Format(DTPicker2.Value, "yyyym") & "'"
Private Sub Command3_Click()
Text3.Text = ""
On Error GoTo handleError
With DataEnvironment1.rsCommand3
.Open "select sum(金额) from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'"
Text3.Text = ![expr1000] & ""
End With
Form_Load_exit:
Exit Sub
handleError:
On Error GoTo 0只能查询一次,再次查询没有反映。要关闭窗体,再次打开窗体才能再次查询。=========================================
问题2
Dim cs As New ADODB.Connection, kj As New ADODB.Recordset '支出情况
If SSTab1.Tab = 0 Then
cs.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;data source=" & App.Path & "\MF.mdb;Mode=ReadWrite"
cs.CursorLocation = adUseClient kj.Open "select sum(金额) from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'", cs, adOpenKeyset, adLockOptimistic
Text3.Text = ![expr1000] & ""
End If中的Text3.Text = ![expr1000] & ""错误,怎么修改
......
With DataEnvironment1.rsCommand3
If .State<>adStateClosed Then .Close ' 先关闭记录集
.Open "select sum(金额) from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'"
......问题2:给 sum(金额) 起个别名,用别名来取字段值:kj.Open "select sum(金额) As 总金额 from richzhichu where format(日期,'yyyymd') >='" & Format(DTPicker1.Value, "yyyymd") & "' and format(日期,'yyyymd')<= '" & Format(DTPicker2.Value, "yyyymd") & "'", cs, adOpenKeyset, adLockOptimistic
Text3.Text = kj![总金额] & "" ' 注意看=后面的写法