thienfu Posted June 23, 2010 Share Posted June 23, 2010 Hello All, i need help query for check date on database access. for example : i need check if value enddate on database < actual date now then system will come out msg box for warning. and for you info record enddate on database using format date like dd/mm/yyyy and i also have problem for read that record enddate. i using below script to read that enddate $test = $dbDatatBase.OpenRecordset("Select enddate From db") but why value show are 20100524000000 actual on database i recorded 24/05/2010 sory for my bad english, hope u all can understand. Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 23, 2010 Share Posted June 23, 2010 So you just want the string refomatted? $test = $dbDatatBase.OpenRecordset("Select enddate From db") ; 20100524000000 $sDate = StringMid($test, 7, 2) & "/" & StringMid($test, 5, 2) & "/" & StringLeft($test, 4) Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
thienfu Posted June 24, 2010 Author Share Posted June 24, 2010 (edited) thanks for reply PsaltyDS, i need help for query check date on database and actual date. example : on my access database have record end_date with below value : No end_date 1 15/05/2010 2 18/06/2010 3 28/06/2010 and today are 24/06/2010. then if i want system just show to me value end_date below today, what query i must do? i have try query like below but system still cannot work. $a = $dbDatatBase.OpenRecordset("Select * From db where end_date < "& _NowDate() &"") if query correct system will only show 2 data ( 15/05/2010 and 18/06/2010 ) sorry for my bad english, hope u can understand Edited June 24, 2010 by thienfu Link to comment Share on other sites More sharing options...
Juvigy Posted June 24, 2010 Share Posted June 24, 2010 NowDate returns month/day/year You need something like: #include <Date.au3> MsgBox(0,'',"The Date is:" &@MDAY&"/"&@MON&"/"&@YEAR) so $a = $dbDatatBase.OpenRecordset("Select * From db where end_date < "& @MDAY&"/"&@MON&"/"&@YEAR &"") Link to comment Share on other sites More sharing options...
thienfu Posted June 24, 2010 Author Share Posted June 24, 2010 NowDate returns month/day/year You need something like: #include <Date.au3> MsgBox(0,'',"The Date is:" &@MDAY&"/"&@MON&"/"&@YEAR) so $a = $dbDatatBase.OpenRecordset("Select * From db where end_date < "& @MDAY&"/"&@MON&"/"&@YEAR &"") Thanks Juvigy for reply, but i've try using u query but still cannot get data i want. for more detail i attach my database and my coding. maybe something wrong on my coding. sorry for my bad english, hope u can understand. expandcollapse popup#include <GUIConstantsEx.au3> #include <Date.au3> #include <GuiListView.au3> #include <FontConstants.au3> #include <WinAPI.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Const $conDbFile = "test.mdb" ; it has to be on script current Dir Const $conTable = "db" $objDBSystem = ObjCreate("DAO.DBEngine.36") $dbDatatBase = $objDBSystem.OpenDatabase(@ScriptDir & "\" & $conDbFile) $recRecordSet = $dbDatatBase.OpenRecordset("Select * From " & $conTable) Global $checkdate, $count GUICreate("Test Check Date", 1000, 700) GUISetState(@SW_SHOW) GUICtrlCreateGroup("Notice", 10, 185, 545, 100) $ResultBox = GUICtrlCreateListView("No| End Date|",15,300,968,340, $LVS_REPORT, $WS_EX_CLIENTEDGE) _GUICtrlListView_SetExtendedListViewStyle($ResultBox, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)) $notice = $dbDatatBase.OpenRecordset("Select * From db where end_date < "& @MDAY&"/"&@MON&"/"&@YEAR &"") if ($notice.Eof = -1 And $notice.Bof =-1) Then EndIf With $notice if $notice.EOF <> true then $count = 0 While Not $notice.EOF for $b = $count to $count Next $count = $count+1 $notice.movenext WEnd EndIf EndWith GUICtrlCreateLabel("Date :", 15, 200) GUICtrlCreateLabel(_NowDate(), 50, 200) GUICtrlCreateLabel("Got ", 15, 220) GUICtrlCreateLabel($count, 40, 220) GUICtrlCreateLabel("Unit Tenant Has End Date and No Yet Terminated / Extend", 55, 220) $viewenddatetenant = GUICtrlCreateButton("View", 350, 218, 30, 20) GUICtrlCreateGroup("", -99, -99, 1, 1) ;close group While 1 $Msg = GUIGetMsg() Switch $Msg Case $GUI_EVENT_CLOSE Exit Case $viewenddatetenant Viewendtenant() EndSwitch WEnd Func Viewendtenant() $resultendtenant = $dbDatatBase.OpenRecordset("Select * From db where end_date <= "& @MDAY&"/"&@MON&"/"&@YEAR &"") if ($resultendtenant.Eof = -1 And $resultendtenant.Bof =-1) Then ; Blank Table $ResultBox = GUICtrlCreateListView("No| End Date|",15,300,968,340, $LVS_REPORT, $WS_EX_CLIENTEDGE) _GUICtrlListView_SetExtendedListViewStyle($ResultBox, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)) Return EndIf With $resultendtenant if $resultendtenant.EOF <> true then $ResultBox = GUICtrlCreateListView("No| End Date|",15,300,968,340, $LVS_REPORT, $WS_EX_CLIENTEDGE) $j=1 While Not $resultendtenant.EOF For $i = $j To $j _GUICtrlListView_SetExtendedListViewStyle($ResultBox, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)) _GUICtrlListView_AddItem($ResultBox, $j) _GUICtrlListView_AddSubItem($ResultBox, $i - 1, .Fields(1).Value, 1) Next $j = $j+1 $resultendtenant.Movenext WEnd GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY") GUISetState() EndIf EndWith EndFunctest.rar Link to comment Share on other sites More sharing options...
Spiff59 Posted June 24, 2010 Share Posted June 24, 2010 Why not load the entire database into memory and then manipulate it from there? Something like: expandcollapse popup#include <GUIConstantsEx.au3> #include <Date.au3> #include <GuiListView.au3> #include <FontConstants.au3> #include <WinAPI.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Const $conDbFile = "test.mdb" ; it has to be on script current Dir Const $conTable = "db" Global $checkdate Global $count Global $Renter_table[1000][2] ; no, end_date Load_Database() GUICreate("Test Check Date", 1000, 700) GUICtrlCreateGroup("Notice", 10, 185, 545, 100) $ResultBox = GUICtrlCreateListView("No| End Date|",15,300,968,340, $LVS_REPORT, $WS_EX_CLIENTEDGE) _GUICtrlListView_SetExtendedListViewStyle($ResultBox, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)) GUICtrlCreateLabel("Date :", 15, 200) GUICtrlCreateLabel(_NowDate(), 50, 200) GUICtrlCreateLabel("Got ", 15, 220) GUICtrlCreateLabel($count, 40, 220) GUICtrlCreateLabel("Unit Tenant Has End Date and No Yet Terminated / Extend", 55, 220) $viewenddatetenant = GUICtrlCreateButton("View", 350, 218, 30, 20) GUICtrlCreateGroup("", -99, -99, 1, 1) ;close group GUISetState(@SW_SHOW) Load_ListView_All() While 1 $Msg = GUIGetMsg() Switch $Msg Case $GUI_EVENT_CLOSE Exit Case $viewenddatetenant Load_ListView_Date("20100525") EndSwitch WEnd Exit ;=============================================================================== Func Load_ListView_All() _GUICtrlListView_DeleteAllItems($ResultBox) For $x = 1 to $count GUICtrlCreateListViewItem($Renter_table[$x][0] & "|" & Display_Date($Renter_table[$x][1]), $ResultBox) Next EndFunc Func Load_ListView_Date($date) _GUICtrlListView_DeleteAllItems($ResultBox) For $x = 1 to $count If $Renter_table[$x][1] < $date Then GUICtrlCreateListViewItem($Renter_table[$x][0] & "|" & Display_Date($Renter_table[$x][1]), $ResultBox) EndIf Next EndFunc Func Display_Date($date) ; Return StringRight($date, 2) & "/" & StringMid($date, 5, 2) & "/" & StringLeft($date, 4) ; DD/MM/YYYY Return StringMid($date, 5, 2) & "/" & StringRight($date, 2) & "/" & StringLeft($date, 4) ; MM/DD/YYYY EndFunc ;=============================================================================== Func Load_Database() $objDBSystem = ObjCreate("DAO.DBEngine.36") $dbDataBase = $objDBSystem.OpenDatabase(@ScriptDir & "\" & $conDbFile) $SQL = "Select * From " & $conTable & ";" $notice = $dbDataBase.OpenRecordset($SQL) While Not $notice.EOF $Renter_table[$count + 1][0] = $notice("no").value $Renter_table[$count + 1][1] = StringLeft($notice("end_date").value, 8) $count += 1 $notice.movenext WEnd $notice.close $objDBSystem = "" ReDim $Renter_table[$count + 1][2] EndFunc Link to comment Share on other sites More sharing options...
thienfu Posted June 25, 2010 Author Share Posted June 25, 2010 (edited) hi spiff59 thanks for the advise. finally i can almost done my project below the script i has modified and can work like i want expandcollapse popup#include <GUIConstantsEx.au3> #include <Date.au3> #include <GuiListView.au3> #include <FontConstants.au3> #include <WinAPI.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Const $conDbFile = "test.mdb" ; it has to be on script current Dir Const $conTable = "db" $objDBSystem = ObjCreate("DAO.DBEngine.36") $dbDatatBase = $objDBSystem.OpenDatabase(@ScriptDir & "\" & $conDbFile) $recRecordSet = $dbDatatBase.OpenRecordset("Select * From " & $conTable) Global $count, $countenddate Global $Renter_table[1000][2] ; no, end_date Global $today = Stringmid(_nowdate(), 7, 4) & Stringmid(_nowdate(), 4, 2) & StringLeft(_Nowdate(), 2) Load_Database() GUICreate("Test Check Date", 1000, 700) GUICtrlCreateGroup("Notice", 10, 185, 545, 100) $a = $dbDatatBase.OpenRecordset("Select * From db") if ($a.Eof = -1 And $a.Bof =-1) Then GUICtrlCreateLabel("There are ", 20, 220) GUICtrlCreateLabel("0", 70, 220) GUICtrlCreateLabel("Unit Tenant Has Been Due Date the lease, but havent confirm want to Extend or Not", 85, 220) $viewenddatetenant = GUICtrlCreateButton("View", 520, 218, 30, 20) Else $countenddate = 0 While Not $a.EOF for $b = $countenddate to $countenddate Next if $a("end_date").value <= $today then $countenddate = $countenddate+1 endif $a.movenext WEnd GUICtrlCreateLabel("There are ", 20, 220) GUICtrlCreateLabel($countenddate, 70, 220) GUICtrlCreateLabel("Units Tenant Has Been Due Date the lease, but havent confirm want to Extend or Not", 85, 220) $viewenddatetenant = GUICtrlCreateButton("View", 520, 218, 30, 20) EndIf $ResultBox = GUICtrlCreateListView("No| End Date|",15,300,968,340, $LVS_REPORT, $WS_EX_CLIENTEDGE) _GUICtrlListView_SetExtendedListViewStyle($ResultBox, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)) GUICtrlCreateLabel("Date :", 15, 200) GUICtrlCreateLabel(_NowDate(), 50, 200) GUICtrlCreateGroup("", -99, -99, 1, 1) ;close group GUISetState(@SW_SHOW) Load_ListView_All() While 1 $Msg = GUIGetMsg() Switch $Msg Case $GUI_EVENT_CLOSE Exit Case $viewenddatetenant Load_ListView_Date($today) EndSwitch WEnd Exit ;=============================================================================== Func Load_ListView_All() _GUICtrlListView_DeleteAllItems($ResultBox) For $x = 1 to $count GUICtrlCreateListViewItem($Renter_table[$x][0] & "|" & Display_Date($Renter_table[$x][1]), $ResultBox) Next EndFunc Func Load_ListView_Date($date) _GUICtrlListView_DeleteAllItems($ResultBox) For $x = 1 to $count If $Renter_table[$x][1] < $date Then GUICtrlCreateListViewItem($Renter_table[$x][0] & "|" & Display_Date($Renter_table[$x][1]), $ResultBox) EndIf Next EndFunc Func Display_Date($date) Return StringRight($date, 2) & "/" & StringMid($date, 5, 2) & "/" & StringLeft($date, 4) ; DD/MM/YYYY ; Return StringMid($date, 5, 2) & "/" & StringRight($date, 2) & "/" & StringLeft($date, 4) ; MM/DD/YYYY EndFunc ;=============================================================================== Func Load_Database() $objDBSystem = ObjCreate("DAO.DBEngine.36") $dbDataBase = $objDBSystem.OpenDatabase(@ScriptDir & "\" & $conDbFile) $SQL = "Select * From " & $conTable & ";" $notice = $dbDataBase.OpenRecordset($SQL) While Not $notice.EOF $Renter_table[$count + 1][0] = $notice("no").value $Renter_table[$count + 1][1] = StringLeft($notice("end_date").value, 8) $count += 1 $notice.movenext WEnd $notice.close $objDBSystem = "" ReDim $Renter_table[$count + 1][2] EndFunc Edited June 25, 2010 by thienfu Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now