Hi all.. I write this article as a reply to Anonymous in the CodeProject discussion board.
Friends, I am very new to programming and have started with a new project which consist of a form which has 2 calendar controls. I wanted to know how can I fetch data that was inputted into a database between two dates and show it into a grid. The date selection is dynamic. Any help will be appreciated.
The program is not something fancy... but I think it can be useful for beginners in VB6 like our Anonymous friend.
I created a database using Access XP and saved it in Access 97 format. It contains one table named
Table1 has 3 columns:
- ID: AutoNumber
- Name: Text .. max. Length=50
- BirthDate: DataTime
The main Form and Controls
frmMain: The main form... nothing special to say...
Data: A data control with properties:
DatabaseName=”Path of database”
Connect=Access (the default)
Cal(): Array of calendar control that contains 2 elements.
(You can create a control array by dropping a control over the form and copy it, then paste it on the form.)
DBGrid: A data Grid control:
Now... let’s come to the easy code needed to update data each time the user selects a new date from a calendar control.
This is the code for
Private Sub Cal_AfterUpdate(Index As Integer)
Dim strDate0 As String
Dim strDate1 As String
Dim strSQL As String
strDate0 = Cal(0).Day & "/" & Cal(0).Month & "/" & Cal(0).Year
strDate1 = Cal(1).Day & "/" & Cal(1).Month & "/" & Cal(1).Year
strSQL = "SELECT * FROM table1 WHERE BirthDate Between #" _
& strDate0 & "# AND #" & strDate1 & "#"
Data.RecordSource = strSQL
That is all the required coding... thanks to the data control and data biding.
How It Works
- I declared two
string variables to hold the dates... I composed them using the calendar control properties.
strSQL is the query used to let the jet engine (which is the component that accesses data) know what data we need.
Debug.Print strSQL is called for debugging purposes, so I can check if the query is formed well.
strSQL value to the
RecordSource property of the data control and refresh data... Then we can see the extracted result set in the grid control.
Any comments are welcome.
- 8th January, 2003: Initial post