日期搜索有关问题(Access)再提.

日期搜索问题(Access)再提..
之前有问过，日期搜索的问题，得到 lxq19851204 的答复是:

VB code

select * from table1 where Cdate(Mid(Caseid,1,4)&"-"&Mid(Caseid,5,2)&"-"&Mid(Caseid,7,2)) between #2011-02-01# and #2011-04-04#

但发现自己用的时候，出问题了: 类型不匹配
WIS_Search_MDB_Str = "Select A.ItemID as ID,B.ItemName as 参数名称,A.ItemValue as 参数值,A.ModifyReason as 修改方式时间 from CaseTParameterHistory A left join [;database=" & WIS_Base_DataBaseConnectName & "].TParameterList B on A.ItemID=B.ItemID where A.ItemID='" & Select_ToolID_Para_ID & "' "
WIS_Search_MDB_Str = WIS_Search_MDB_Str & " and CDate(Mid(A.ModifyReason, 1, 4) & " - " & Mid(A.ModifyReason, 5, 2) & " - " & Mid(A.ModifyReason, 7, 2)) between #" & Pre_T.Value & "# and #" & Post_T.Value & "# order by A.ModifyReason"

不知道:

SQL code

CDate(Mid(A.ModifyReason, 1, 4) & " - " & Mid(A.ModifyReason, 5, 2) & " - " & Mid(A.ModifyReason, 7, 2)) between #" & Pre_T.Value & "# and  #" & Post_T.Value & "#

这句有何问题?

[解决办法]
WIS_Search_MDB_Str = "Select A.ItemID as ID,B.ItemName as 参数名称,A.ItemValue as 参数值,A.ModifyReason as 修改方式时间 from CaseTParameterHistory A left join [;database=" & WIS_Base_DataBaseConnectName & "].TParameterList B on A.ItemID=B.ItemID where A.ItemID='" & Select_ToolID_Para_ID & "' "
WIS_Search_MDB_Str = WIS_Search_MDB_Str & " and CDate(Mid(A.ModifyReason, 1, 4) & " - " & Mid(A.ModifyReason, 5, 2) & " - " & Mid(A.ModifyReason, 7, 2)) between #" & Pre_T.Value & "# and #" & Post_T.Value & "# order by A.ModifyReason"

把上面的这个字符串用SQL语句给显示出来
[解决办法]
between #" & format(Pre_T.Value,"YYYY-MM-DD") & "# and #" & format(Post_T.Value,"YYYY-MM-DD") & "#
[解决办法]
debug.print WIS_Search_MDB_Str
看看这个语句是什么？
[解决办法]

SQL code

Select A.ItemID as ID,A.ItemValue as 参数值,A.ModifyReason as 修改方式时间  from CaseTParameterHistory AWhere CDate(Mid(A.ModifyReason, 1, 4) & " - " & Mid(A.ModifyReason, 5, 2) & " - " & Mid(A.ModifyReason, 7, 2)) between #2011-07-30# and  #2011-07-30#
[解决办法]
VB code
WIS_Search_MDB_Str = ""WIS_Search_MDB_Str = "Select ItemID as ID,ItemValue as 参数值,ModifyReason as 修改方式时间  from CaseTParameterHistory where ItemID='" & Select_ToolID_Para_ID & "'  "WIS_Search_MDB_Str = WIS_Search_MDB_Str & " and CDate(Mid(ModifyReason, 1, 4) & '-' & Mid(ModifyReason, 5, 2) & '-' & Mid(ModifyReason, 7, 2)) between #2011-05-01# and  #2011-07-31# "
[解决办法]
探讨

VB code

CDate(Mid(ModifyReason, 1, 4) & '-' & Mid(ModifyReason, 5, 2) & '-' & Mid(ModifyReason, 7, 2))

如果单独去测试这句，本身就会出错.

另外整体测试:报错 日期使用语法错误!
 
 
[解决办法]
这是我的测试代码：
VB code
Dim rs As ADODB.RecordsetDim cn As New ADODB.ConnectionDim WIS_SEARCH_MDB_STR As StringWIS_SEARCH_MDB_STR = ""WIS_SEARCH_MDB_STR = "SELECT *  from c where 1=1 "WIS_SEARCH_MDB_STR = WIS_SEARCH_MDB_STR & " and CDate(Mid(ModifyReason, 1, 4) & '-' & Mid(ModifyReason, 5, 2) & '-' & Mid(ModifyReason, 7, 2)) between #2011-05-01# and  #2011-07-31# "cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False"Set rs = cn.Execute(WIS_SEARCH_MDB_STR)MsgBox rs("ModifyReason")rs.Closecn.Close