Filter listbox with date range criteria

[vent]

Hi everyone

So i have a list box that's row source is bound to a query (qryCompanyAndAssoc) that filters company information. There's two columns in this list box that display dates. Right now I'm trying to implement a date range filter, where the user selects a from and to date via two text boxes and the listbox would display the results. This is what I have so far:

Private Sub Command81_Click()

'Search button

Call Search

End Sub



Sub Search()



Dim srchCriteria, task As String



Me.Refresh

If IsNull(Me.txtFrom) Or IsNull(Me.txtTo) Then

MsgBox "Please enter the date range", vbInformation, "Date Range Required"

Me.txtFrom.SetFocus

Else

srchCriteria = "([AAEndDate] >= #" & Me.txtFrom & "# And [AAEndDate] <= #" & Me.txtTo & "#)"

task = " SELECT * FROM qryCompanyANDAssoc WHERE (" & srchCriteria & ") order by [AAEndDate]"

DoCmd.ApplyFilter task

End If

End Sub

AAEndDate = Association Agreement End Date
txtFrom = Date From
txtTo = Date To

The text boxes are set to Short Date format and when I select a date range then click search I'm given an error saying "Error: 2491 The action or method is invalid because the form or report isn't bound to a table or query" and DoCmd.ApplyFilter task is highlighted yellow.

If anyone has some suggestions or advice on how to solve this, a rookie like me, I would really appreciate it!

 

[Minty]

The filter property applies to the forms underlying recordset. By the sound of it you are trying to filter the listboxes rowsource, which doesn't have a filter property.

I think you need to update the listboxes rowsource to be your task string.

 

[vent]

The filter property applies to the forms underlying recordset. By the sound of it you are trying to filter the listboxes rowsource, which doesn't have a filter property.

I think you need to update the listboxes rowsource to be your task string.

So I deleted the DoCmd.ApplyFilter task and replaced qryCompanyAndAssoc with the listbox name (lstSearchResults). The error is gone but the listbox itself doesn't do anything. Not sure what I'm still missing.

Also, would this method work for both date columns or do I have to include the other date column in the query?

 

[Minty]

I think you would simply need

Me.lstSearchResults.RowSource = task

 

[vent]

I think you would simply need

Me.lstSearchResults.RowSource = task

I tried that and nothing shows up when I select two dates

 

[vent]

Sub Search()

Dim srchCriteria, task As String

Me.Refresh
If IsNull(Me.txtFrom) Or IsNull(Me.txtTo) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.txtFrom.SetFocus
Else
srchCriteria = "([=lstSearchResults.[Column](2)] >= #" & Me.txtFrom & "# And [=lstSearchResults.[Column](2)] <= #" & Me.txtTo & "#)"<= #" & Me.txtTo & "#)"

Me.lstSearchResults.RowSource = task
Me.lstSearchResults.Requery
End If
End Sub


=lstSearchResults.Column(2) is the AAEndDate column, I've also tried [tblCompany].[AAEndDate] but that didn't bear fruit
These are the changes so far

 

[Minty]

You've changed your code so now task doesn't have the rest of your SQL statement. Add that back. The also add debug.Print task before you set the rowsource. You should see in the debug window your query string.

Sent from my Nexus 7 using Tapatalk

 

[vent]

I changed it back to

srchCriteria = "([AAEndDate] >= #" & Me.txtFrom & "# And [AAEndDate] <= #" & Me.txtTo & "#)"

And also added Debug.Print task before setting the record source.But still no avail. I will still keep playing with it. Thank you for sharing your knowledge

 

[isladogs]

As minty said earlier, you no longer have anything to define 'task'
I expect your debug.print line was blank

In your srchCriteria, remove the ()
I also suggest you use Between to simplify the code in place of <= and >=

 

[vent]

Hey guys

A thought just occurred to me. Am I doing more than necessary? I was thinking if i set the form's record source to be the query qryCompanyAndAssoc then the two text boxes could be bound to the two dates date columns in the query, but then my question is, how would the list box auto populate based on whatever dates the user selects on the calendar? Do I still need to implement a search button?

 

[Minty]

You can move the criteria into your query, you would then just need to requery the listbox on the after update event of both the datefrom and dateto controls.

I personally would also make sure you have some default values in those boxes - maybe the start and end of the current month to ensure you get some results on form load.

 

[The_Doc_Man]

You should ALWAYS do a control.Requery if you are changing the contents of a control.ControlSource or control.RowSource dynamically.

When I did this sort of thing in the past, I had "LostFocus" events on the relevant controls. They kept a copy of what was in the controls from a previous iteration. The form's OnCurrent routine would reset the "memory" of those values. Then, when somone changed the filter, the code could easily test whether something had changed. If it did, the LostFocus routine called a little subroutine to rebuild the SQL for the main control that was responding to those parameter changes.

The sub was quite easy. It was always just a matter of string manipulation to take a constant front part (the SELECT and FROM clauses), the variable middle part (the WHERE clause) and the constant back part (the ORDER BY clause). Since it was a sub in the form's Class module, it could contain Me.controlname as a way to pick up the values needed, reformat them into the required date format and punctuation, and just drop the resulting string into the right place.

 

[vent]

I should mention I have a search bar that filters the list box based on what the user types. This following SQL statement selects the all important columns but the search bar filters through Company and/or Association values. The two date columns I would like the listbox to filter by are AAEndDate and ISExpiry. The listbox's rowsource is set to the query that uses this SQL statement. How do I make the suggested changes without messing up the already functioning search bar. Do the changes need to be made in the AfterUpdate event? I ask simply because I don't want any new changes to mess up what already works.

SELECT tblCompany.CompanyName, tblAssociation.Associate, tblCompany.AAEndDate, tblCompany.ISExpiry, tblCompany.ISDeclaration, tblCompany.Address, tblCompany.City, tblCompany.State, tblCompany.ZipCode, tblCompany.ContactName, tblCompany.ContactEmail, tblCompany.ContactPhoneNumber, tblInstitution.Program
FROM tblInstitution INNER JOIN (tblAssociation INNER JOIN tblCompany ON tblAssociation.ID = tblCompany.[Associate(s)].Value) ON tblInstitution.ID = tblCompany.Programs.Value
WHERE (((tblCompany.CompanyName & tblAssociation.Associate & tblCompany.AAEndDate & tblCompany.ISExpiry) Like "*" & [forms]![frmMain]![SrchTxt] & "*"))
ORDER BY tblCompany.CompanyName;


Thank you

 

[Minty]

Use the query designer and the expression builder in it. The SQL you have posted will not work. Each aspect of the where clause should be a separate logical comparison.

Go back to your original query. If necessary go to the SQL view and copy it to a text editor for back up and reference. Click the builder whilst you are in the criteria section of AAEndDate use the builder to get the forms textboxes into a sensible expression. Rinse and repeat for the IsExpiry. Check the results in the datasheet view if you have the form open, it will reflect the results you should see.

 

[vent]

Use the query designer and the expression builder in it. The SQL you have posted will not work. Each aspect of the where clause should be a separate logical comparison.

Go back to your original query. If necessary go to the SQL view and copy it to a text editor for back up and reference. Click the builder whilst you are in the criteria section of AAEndDate use the builder to get the forms textboxes into a sensible expression. Rinse and repeat for the IsExpiry. Check the results in the datasheet view if you have the form open, it will reflect the results you should see.

In the builder criteria section of AAEndDate I did this >=#1/1/2000# And #12/31/2009#

For the text boxes I have them set as the default values of #1/1/2000# and #12/31/2009# date from and date to respectively. Is this what you mean by "sensible expression?"

Thank you.

 

[The_Doc_Man]

>=#1/1/2000# And #12/31/2009#

While syntactically this expression has a meaning due to rules on automatic variable promotion of type, I am quite sure it is not the meaning you might have wanted. There will be a bitwise logical AND function in here somewhere between either two dates or between a date and a Boolean value that gets promoted. I'm sure the result will be strange no matter what. OR you might get error 13 (Type Mismatch) from that one.

What was your intent for the second date? Or did you leave something out when you transcribed the code to this thread?

 

[Minty]

Nope that wasn't what I meant.

You can refer to the form directly in the query criteria. if you type forms! in the criteria section you should then see a list of your available forms come up, pick the form with your date from and to then pick the textbox with the from date etc.

In the criteria under AAEndDate you want it to end up looking something like

Between [forms]![frmMain]![txtFrom] And [forms]![frmMain]![txtTo]

You would then copy and paste the same to your other date criteria.
You can them also add in the criteria for your search bar.

 

[vent]

Nope that wasn't what I meant.

You can refer to the form directly in the query criteria. if you type forms! in the criteria section you should then see a list of your available forms come up, pick the form with your date from and to then pick the textbox with the from date etc.

In the criteria under AAEndDate you want it to end up looking something like

Between [forms]![frmMain]![txtFrom] And [forms]![frmMain]![txtTo]

You would then copy and paste the same to your other date criteria.
You can them also add in the criteria for your search bar.

I'm presently facepalming myself because I didn't know it was that simple. Real rookie mistake but least I'm learning. Thank you so much!