need help query for check month on database access

thienfu · 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 · 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)

:mellow:

thienfu · June 24, 2010

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 · 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 · 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.

#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

Spiff59 · June 24, 2010

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

thienfu · June 25, 2010

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 June 25, 2010 by thienfu

Sign In

need help query for check month on database access

Recommended Posts

thienfu

Link to comment

Share on other sites

PsaltyDS

Link to comment

Share on other sites

thienfu

Link to comment

Share on other sites

Juvigy

Link to comment

Share on other sites

thienfu

Link to comment

Share on other sites

Spiff59

Link to comment

Share on other sites

thienfu

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta