Sign in to follow this  
Followers 0
thienfu

need help query for check month on database access

7 posts in this topic

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.

Share this post


Link to post
Share on other sites



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)

:mellow:


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

Share this post


Link to post
Share on other sites

#3 ·  Posted (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 by thienfu

Share this post


Link to post
Share on other sites

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 &"")

Share this post


Link to post
Share on other sites

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.

#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
EndFunc

test.rar

Share this post


Link to post
Share on other sites

Why not load the entire database into memory and then manipulate it from there?

Something like:

#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

Share this post


Link to post
Share on other sites

#7 ·  Posted (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 :mellow:

#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 by thienfu

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0