'以 SQL 2K 的 Northwind 为例 Dim adoConnection As New ADODB.Connection Dim adoRecordset As New ADODB.Recordset adoConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=server\psql2ke" adoRecordset.Open "select * from [Orders]", adoConnection, adOpenKeyset Debug.Print adoRecordset.RecordCount adoRecordset.Filter = "OrderDate='1996-07-04'" If Not adoRecordset.EOF Then Debug.Print adoRecordset.GetString End If Debug.Print adoRecordset.RecordCount
to playyuer SQL中一个表A中有一datetime 型字段F,值如:2001-08-04 11:02:35.000
rs.Filter = "OrderDate between '2001-8-9' and '2001-8-10'" 当然,动态设定日期需用变量连接上述字符串,但必须用between...and...,因为datetime是包含时间的,如用OrderDate = '2001-8-9'可能一个记录都选不出来。
你在VB中用以下语句生成记录集RS: rs.open "select convert(char(10),F,21) from A" 然后就可以用: rs.Filter="F='2001-08-07'" 来查找记录了.
应该改为: rs.open "select convert(char(10),F,21) F from A" 太粗心!:)
Hello! 如果非要用Filter那么就这么解决: 把你的Rs.Filter = "日期字段 = #2001-05-11#" 你的程序修改如下: Dim Sql As String Dim Conn As New ADODB.Connection, Rs As New ADODB.Recordset, Rst As New ADODB.Recordset Sql = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=server\psql2ke" Conn.Open (Sql) Sql = "select * from A" Set Rs = Conn.Execute(Sql) Rs.Filter = "fsrq = #2001-05-11#" Set Rst = Rs While Not Rst.EOF Debug.Print Rst(0) Rst.MoveNext Wend .................... 此程序在VB中调试通过 主要是在Filter中时间是用#Date#表示的!
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
adoConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=server\psql2ke"
adoRecordset.Open "select * from [Orders]", adoConnection, adOpenKeyset
Debug.Print adoRecordset.RecordCount
adoRecordset.Filter = "OrderDate='1996-07-04'"
If Not adoRecordset.EOF Then
Debug.Print adoRecordset.GetString
End If
Debug.Print adoRecordset.RecordCount
SQL中一个表A中有一datetime 型字段F,值如:2001-08-04 11:02:35.000
当然,动态设定日期需用变量连接上述字符串,但必须用between...and...,因为datetime是包含时间的,如用OrderDate = '2001-8-9'可能一个记录都选不出来。
rs.open "select convert(char(10),F,21) from A"
然后就可以用:
rs.Filter="F='2001-08-07'"
来查找记录了.
rs.open "select convert(char(10),F,21) F from A"
太粗心!:)
如果非要用Filter那么就这么解决:
把你的Rs.Filter = "日期字段 = #2001-05-11#"
你的程序修改如下:
Dim Sql As String
Dim Conn As New ADODB.Connection, Rs As New ADODB.Recordset, Rst As New ADODB.Recordset
Sql = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=server\psql2ke"
Conn.Open (Sql)
Sql = "select * from A"
Set Rs = Conn.Execute(Sql)
Rs.Filter = "fsrq = #2001-05-11#"
Set Rst = Rs
While Not Rst.EOF
Debug.Print Rst(0)
Rst.MoveNext
Wend
....................
此程序在VB中调试通过
主要是在Filter中时间是用#Date#表示的!
辛苦了 10分 (在VB板块),这个问题我已经解决,但新的问题来了,还有50分
大家可以自由发挥,我只要结果把表1:
字段A 字段B 字段C
2001-08-01 233 111.00
2001-08-05 132 222.12和表2:
字段D 字段E
2001-08-03 123
2001-08-05 545查询出如下型式:
字段A 字段B 字段C 字段E
2001-08-01 233 111.00 NULL
2001-08-03 NULL NULL 123
2001-08-05 132 222.12 545
union
Select t2.D as A,NUll as B,Null as C,t2.E as E From t2
order by t2.D