Jump to content
Sign in to follow this  
thienfu

need help query for check month on database access

Recommended Posts

thienfu

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
PsaltyDS

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
thienfu

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
Juvigy

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
thienfu

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
Spiff59

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
thienfu

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.