|
||
|
*停權中*
加入日期: Nov 2010
文章: 116
|
想請教一個資料庫的時間跨日搜尋寫法
-不好意思原始碼我忘記COPY回來看了-
我用VB寫一個去搜尋ACCESS的資料庫，項目有"料號、日期、時間、資料"等 原本搜尋日期我是可以搜尋 XX天到XX天之間的沒問題，針對料號搜尋也是沒問題，料號跟日期一起(AND起來)搜尋也沒問題 可是最麻煩的是時間，我還沒機會寫寫看所以沒辦法確認，可是我覺得要是我搜尋 "5月1日晚上21:00~5月2日晚上1:00"；他可能的回傳值是5月1日和5月2日早上1:00~21:00間所有資料，想問一下有沒有人寫過，應該怎麼避開這問題? |
|||||||
2011-07-03, 11:02 AM
#1
|
|
|
Regular Member
 加入日期: Aug 2005
文章: 72
|
問題應該是在於，資料庫認不認識你傳入的日期參數，跟VB未必有關。
例如，Aceess 認不認識: select ... from ... where 某欄位 between cdate("2011/5/1 21:00PM") and cdate("2011/5/2 01:00AM") |
||
|
2011-07-04, 07:45 PM
#2
|
|
|
*停權中*
加入日期: Nov 2010
文章: 116
|
搞定了
原先條件是這樣的 sql = "Select * from Product where PartName= '" & Team(25) & "' and Produdate Between #" & STARTDAY & "# And #" & ENDDAY & "# Order by Produdate,ProduTime,PartName " Team(25)=物件名稱 Produdate=日期 ProduTime=時間 後來我改成這樣後有個問題，超過一天他的紀錄方式會不對 sql = "Select * from Product where PartName= '" & Team(25) & "' and Produdate Between #" & STARTDAY & "# And #" & ENDDAY & "# and ProduTime Between #" & STARTTIME & "# And #" & ENDTIME & "# Order by Produdate,ProduTime,PartName " 後來我改成兩段式寫法就OK了 IF STARTDAY=ENDDAY then sql = "Select * from Product where PartName= '" & Team(25) & "' and Produdate Between #" & STARTDAY & "# And #" & ENDDAY & "# and ProduTime Between #" & STARTTIME & "# And #" & ENDTIME & "# Order by Produdate,ProduTime,PartName " elae sql = "Select * from Product where PartName= '" & Team(25) & "' and Produdate Between #" & STARTDAY & "# And #" & STARTDAY & "# and ProduTime Between #" & STARTTIME & "# And #" & 23:59:59 & "# Order by Produdate,ProduTime,PartName " .............. sql = "Select * from Product where PartName= '" & Team(25) & "' and Produdate Between #" & (STARTDAY+1) & "# And #" & ENDDAY & "# Order by Produdate,ProduTime,PartName " END IF |
|
2011-07-06, 12:39 PM
#3
|
|
