thienfu Posted June 23, 2010 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.
PsaltyDS Posted June 23, 2010 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
thienfu Posted June 24, 2010 Author 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
Juvigy Posted June 24, 2010 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 &"")
thienfu Posted June 24, 2010 Author 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
Spiff59 Posted June 24, 2010 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
thienfu Posted June 25, 2010 Author 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
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