How do you filter all the Records between two dates 5

[Hiccup]

My setup is VB6/Access2K. I have a Field named "DepositDate" in an Access Table "DepositsDatabase" and need to filter all Records from the Table that fall within two dates (including the end dates) that the user enters on the VB Form, "Starting Date" txtBox and "Ending Date" txtBox and then have a DataGrid on the Form populate with only those Records. The DataGrid is connected to the Table via an Adodc.

Any code suggestions how to accomplish this. I guess I need to make a connection through the Adodc to the Access mdb Table in the code first, right?

Thanks in advance!

 

[Hiccup]

DrJavaJoe...you seem to be the VB Guru. Maybe you can help me one more time trying to print a DataReport of the Records that are returned when I run the From/To date search code you've helped me with.

I've added the code below to a cmdPrint Button that's on the Form with the txtBoxes and grdList DataGrid. I'm trying to print the search results onto a DataReport named "RentRoll2."

Private Sub cmdPrint_Click(Index As Integer)
OpenReport1
End Sub
Private Sub OpenReport1()
Dim rpt As RentRoll2
Set rpt = New RentRoll2
rpt.Display Me.grdList.rs, vbModal
Set rpt = Nothing
End Sub

After the grdList is filled with the Records, if I click the cmdPrint Button to print out the DataReport, I get this compile error: Method or data member not found.

The code for populating the grdList with the Records between the user entered dates is shown below and is working fine (Thanks to you!)

Option Explicit
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'Dim rs_searchResult As New ADODB.Recordset
Private Sub cmdSearch_Click(Index As Integer)
'Reopen recordset with filtered data
Call FindData
'cmdPrint.Enabled = True
End Sub
Private Sub Form_Load()
If IsDate(txtDateFrom.Text) And IsDate(txtDateTo.Text) Then
'Now Get all the records from the database
Call FindData
End If
End Sub
Private Sub GetConnection()
If Cn.State = 0 Then
Dim ConnectionString As String
'Database should be in the Application's Path
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EHicks\RESIDENTIAL PROPERTY MANAGER\RPM.mdb;Persist Security Info=False"
'First Open the Connection before Using it in Recordset
Cn.CursorLocation = adUseClient
Cn.Open ConnectionString
End If
End Sub
'This sub is used to open a recordset with data that matched the search criteria
'and then shows that to a datagrid named "grdList"
Private Sub FindData()
Dim SQLString As String
'Use the Search Criteria in the Query
SQLString = "SELECT * FROM SortRentDeposits WHERE DepDate BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & "# ORDER BY DepDate"

'Close the recordset if it is already open
If rs.State = 1 Then
rs.Close
End If
'Now Open Recordset again with SQLString
Call GetConnection
rs.Open SQLString, Cn, adOpenStatic, adLockOptimistic
'Bind Recordset with Grid
Set grdList.DataSource = rs
End Sub

Again, can I impose on you for any suggestions?

Thanks in advance!

 

[johnwm]

You'll probably do better to ask this entirely new question in a new thread. Make sure you check out faq222-2244 regarding asking questions specifically addressed to another member!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

Essex Steam UK for steam enthusiasts

 

[Hiccup]

DrJavaJoe - I forgot to mention that I have this code on the DataReport:

Option Explicit
Private rsDataReport As ADODB.Recordset

Private Sub DataReport_Terminate()
If Not rsDataReport Is Nothing Then
If rsDataReport.State = adStateOpen Then rsDataReport.Close
End If
Set rsDataReport = Nothing
End Sub
Public Sub Display(rs As ADODB.Recordset, Optional vbModalType As VBRUN.FormShowConstants = vbModal)
Set rsDataReport = Nothing
Set rsDataReport = rs.Clone
Set Me.DataSource = rsDataReport
Me.Show vbModalType
End Sub

johnwm - You're right. I intend, after I get the DataReport to print out correctly, to create an "Info" thread describing how this is done.

Thanks! Hiccup

 

[DrJavaJoe]

I'm going to agree with johnwm on both points, first you should start a new thread for help on the datareport question. I haven't used the built in Datareport in 4 years, I prefer Crystal, so if I saw a thread asking a question about it I would most likely skip over it which leads to his second point, hey but thanks for the complements



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Hiccup]

Thanks DrJavaJoe, et al for the help. I figured out why the DataReport wasn't printing.....it was "operator Error" ...copied/pasted too much in this line:

rpt.Display Me.Adodc1.rs, vbModal

It should have been:

rpt.Display rs, vbModal

The date search code and the DataReport print code works fine now!

I'll post a new thread to cover the DataReport printing "How To" of the search results.

Thanks again!