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!

 

[Sheco]

Well instead of connecting to the table you can connect with an SQL SELECT command and then put your criteria into the WHERE clause.

Also, and this is just a guess, you might be able to set the .Filter property of the adodc RecordSet to accomplish the same result.

 

[zemp]

Use an SQL statement, something similar to the following, as the recordsource for the ADODC.

"Select * From Table1 Where TheDate Between #" & StartDate & "# And #" & enddate & "#"

The '#' character deliminates a date value for Access.

zemp

 

[Hiccup]

Thanks zemp for your suggestion. I tried your code, but I am having some problems getting what I need. I need the click event to populate a DataGrid on a different From with only the Records with a deposit date that occurred from the DateFrom.txt thru the DateTo.txt that the user enters.

This is the code (incorporating the code you suggested):

Option Explicit
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub cmdSearch_Click()
Call FindData(txtDateFrom.Text, txtDateTo.Text)
Command1.Enabled = True
End Sub
Private Sub Form_Load()
Call FindData
End Sub
Private Sub GetConnection()
If Cn.State = 0 Then
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EHicks\RESIDENTIAL PROPERTY MANAGER\RPM.mdb;Persist Security Info=False"
Cn.CursorLocation = adUseClient
Cn.Open ConnectionString
End If
End Sub
Private Sub FindData()
Dim SQLString As String
SQLString = "SELECT * FROM SortRentRoll WHERE DatePymtRcvd BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & " ORDER BY DepNos"
If rs.State = 1 Then
rs.Close
End If
Call GetConnection
rs.Open SQLString, Cn, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = rs
End Sub

When I click the cmdButton, I get a syntax error in the date query expression.

Any suggestions?

Thanks in advance!

 

[RoyVidar]

One little missing #...

[tt]... txtDateTo.Text & "# ORDER BY DepNos"[/tt]

Roy-Vidar

 

[fredericofonseca]

SQLString = "SELECT * FROM SortRentRoll WHERE DatePymtRcvd BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & " ORDER BY DepNos"

is mising a #

change to
SQLString = "SELECT * FROM SortRentRoll WHERE DatePymtRcvd BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & "# ORDER BY DepNos"

And whenever possible don´t use bound controls. Load the records manually.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Hiccup]

Thanks you guys.. a star for each of you for your help!...all it takes is one little missing #!!!

 

[Hiccup]

Hmmmm....per your suggestions, I changed the SELECT statement to:

SQLString = "SELECT * FROM SortRentRoll WHERE DatePymtRcvd BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & "# ORDER BY DepNos"

And I'm still getting the syntax error in the date query expression.

Any ideas anyone?

Thanks in advance!

 

[RoyVidar]

From the names of fields, variables... I'm assuming you are on either UK or US date settings, which means with valid dates, one shouldn't get that error, but in any case, formatting the dates could be something to try:

[tt]...AND #" & format$(txtDateTo.Text,"mm\/dd\/yyyy") & "#...[/tt]

Else, I'd check whether the two textboxes 1, contained data and 2, contained valid dates (IsDate?)

Roy-Vidar

 

[Hiccup]

Thanks, Roy-Vidar...I'm on US date format, but I'll give formating the dates per your suggestion a try. Thanks!

 

[Hiccup]

Hmmmm....added the date format code, but I'm still getting the same date syntax error. Maybe my setup is the problem. I can't even get to the VB Form that contains the txtDateFrom and txtDateTo text boxes without getting the syntax error.

I have a Form with cmdButtons as menu items. One of them has a click event that takes you to the Form for entering the From/To dates; it's this Form that contains the above code. That Form also has a cmdButton named cmdGenerateReport that will display a third Form containing the Adodc and its DataGrid. But because of the syntax error, you can't even get to the Form for specifying the From/To dates.

Maybe I should be tieing the above code to the cmdGenerateReport Button rather than where I have it.

Any thoughts?

 

[Hiccup]

Gentlemen, I've used code similar to that described above and it works fine to "Filter" all the Records into a DataGrid based on the user's input into a txtBox. But, there's something in this SELECT code that's giving me the syntax Date error when I try to access the From.

Well I've completed changed the setup. I now have the DataGrid, txtBoxes (for entering the From and To dates) and the cmdButton for filtering all the Records with a date on or between the From and To dates. And I'm still getting a Date format syntax error...now it reads error in date in query expression "DepDate BETWEEN ## AND ##'. I get this error when I click a cmdButton (on another Form) to access the Form containg the problem code.

My SELECT statement is now formatted (I'm sure cause I've double-checked it numerous times) as you gentlemen have suggested. It is:

SQLString = "SELECT * FROM SortRentDeposits WHERE DepDate BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & "# ORDER BY DepDate"

I'm stumped....."HELP" Please!

Thanks in advance

 

[DrJavaJoe]

Don't allow the code to execute if txtDateFrom.Text and txtDateTo.Text is blank or not a valid date.



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

 

[johnwm]

Your error message means that txtDateFrom.Text and txtDateTo.Text is empty. Are the textboxes on the same form as the cmd button? - are the names spelled correctly? If so you might look at disabling the cmd button until the textboxes have valid values. You can set/clear a form level boolean in the validate event of the text boxes and use that to enable the cmd button.

________________________________________________________________
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]

Thanks DrJavaJoe and johnwm. I suspected that the message was because the From & To txtBoxes were empty.

The DepositsReport Form contains the txtBoxes for entering the "From" and "To" dates, the DataGrid "grdList" and a cmdSEARCH Button for selecting all of the Records falling on or between the From and To dates.

I access this DepositsReport Form from another Form and immediately get the date syntax error because I don't even get the DepositsReport Form to display so that I can enter the From and To dates.

DrJaveJoe, your statement "Don't allow the code to execute if txtDateFrom.Text and txtDateTo.Text is blank or not a valid date." sounds like that's what I need to do. Can you explain what I need to do to the code on the DepositsReport Form to do this, or is it done somehow with code on the Form that accesses the DepositsReport Form?

Thanks for the help! And here's a star for both of you!

 

[DrJavaJoe]

In your form load event add something like:
if IsDate(txtDateFrom.Text) and Isdate(txtDateTo.Text ) then
Call FindData
endif

or populate the two textboxes with a default value:

txtDateFrom.Text = Date
txtDateTo.Text = Date
Call FindData






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]

DrJavaJoe - your "if IsDate(txtDateFrom.Text) and Isdate(txtDateTo.Text ) then
Call FindData
endif" did the trick.....I owe you a BIG one. This has been driving me crazy! Here's another star!

 

[Hiccup]

DrJavaJoe, thanks again. The Form displays fine and when I enter the From/To dates and click the Generate Report Button I get this compile error: Wrong number of arguments or invalid property assignments. And the following code is highlighted:

Private Sub cmdSearch_Click(Inex As Integer)
'Reopen recordset with filtered data
Call FindData(txtDateFrom.Text, txtDateTo.Text)
cmdPrint.Enabled = True
End Sub

It doesn't like how I've setup my user input boxes for the From/To dates apparently.

Any thoughts on how to set this one up?

Thanks in advance!

 

[DrJavaJoe]

The FindData function definition has no arguements change Call FindData(txtDateFrom.Text, txtDateTo.Text)
to Call FindData()




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]

You the man, DrJavaJoe. That did the trick and everything is working fine!

Thanks...and another star!