Introduction
Hi all.. I write this article as a reply to Anonymous in the CodeProject discussion board.
Anonymous wrote:
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.
The Project
I created a database using Access XP and saved it in Access 97 format. It contains one table named table1
.
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:
Visible=False
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 Cal_AfterUpdate
:
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 & "#"
Debug.Print strSQL
Data.RecordSource = strSQL
Data.Refresh
End Sub
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.
- Assign
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.
History
- 8th January, 2003: Initial post