日期搜索有关问题(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 A
Where 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))

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

另外整体测试:报错 日期使用语法错误!