Results 1 to 12 of 12
  1. #1
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76

    Filter listbox with date range criteria

    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 (set to short date) and the list box 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 = "([=lstSearchResults.[Column](2)] >= #" & Me.txtFrom & "# And [=lstSearchResults.[Column](2)] <= #" & Me.txtTo & "#)"<= #" & Me.txtTo & "#)"

    Me.lstSearchResults.RowSource = task
    Me.lstSearchResults.Requery
    End If
    End Sub
    Whenever I select two dates and hit "Search" the entire list box disappears and nothing happens. Also, would this method work for both date columns or do I have to include the other date column in the query? If anyone has any information. A rookie like me would really appreciate it!

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

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Only the bound column is stored in a list box I think. Could you use a combobox instead

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of putting dates in a list box,
    can you just put 2 text boxes on the form, txtStartDate and txtEndDate.
    the the query can use the form for the dates. NO SQL.

    ie: select * from table where date betweeen forms!myForm!txtStartDate and forms!myForm!txtEndDate

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The issue is what's in the task variable when you set it as the row source (hint...nothing).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Quote Originally Posted by ranman256 View Post
    instead of putting dates in a list box,
    can you just put 2 text boxes on the form, txtStartDate and txtEndDate.
    the the query can use the form for the dates. NO SQL.

    ie: select * from table where date betweeen forms!myForm!txtStartDate and forms!myForm!txtEndDate

    You mean setting the form's record source to the query? Also would that select statement be written in the SQL view?

  6. #6
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Also I have two buttons, one is a search button and the other is a reset button. Could simply select a start date and end date filter the list box automatically?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I assume you didn't notice post 4, so I'll clarify. You aren't setting the task variable to anything. You'd want something like this (note changes to your string in red):

    Code:
    srchCriteria = "[FieldName] >= #" & Me.txtFrom & "# And [FieldName] <= #" & Me.txtTo & "#"
    task = "SELECT Blah FROM TableName WHERE " & srchCriteria
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02.........

    You could
    - set the default property of the text box "txtFrom" to #1/1/1901#
    - set the default property of the text box "txtTo" to #12/31/2099#

    Change the name of the buttons and add this click code:
    Code:
    Private Sub btnSearch_Click()
      'Search button
      Me.lstSearchResults.Requery
    End Sub
    
    
    Private Sub btnReset_Click()
      'Reset button
      Me.txtFrom = #1/1/1901#
      Me.txtFrom = #12/31/2099#
      Me.lstSearchResults.Requery
    End Sub
    Set the list box " lstSearchResults" RowSource to:
    Code:
    SELECT Field1, Field2 FROM TableName WHERE [FieldName] Between [forms]![YourForm].[txtFrom] And [forms]![YourForm].[txtTo]));
    Change one of both dates and click the search button.
    Click the reset button to reset the dates to default and requery the list box.

  9. #9
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    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 the 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
    Last edited by vector39; 05-31-2017 at 09:18 AM. Reason: more detail

  10. #10
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Quote Originally Posted by ssanfu View Post
    My $0.02.........

    You could
    - set the default property of the text box "txtFrom" to #1/1/1901#
    - set the default property of the text box "txtTo" to #12/31/2099#

    Change the name of the buttons and add this click code:
    Code:
    Private Sub btnSearch_Click()
      'Search button
      Me.lstSearchResults.Requery
    End Sub
    
    
    Private Sub btnReset_Click()
      'Reset button
      Me.txtFrom = #1/1/1901#
      Me.txtFrom = #12/31/2099#
      Me.lstSearchResults.Requery
    End Sub
    Set the list box " lstSearchResults" RowSource to:
    Code:
    SELECT Field1, Field2 FROM TableName WHERE [FieldName] Between [forms]![YourForm].[txtFrom] And [forms]![YourForm].[txtTo]));
    Change one of both dates and click the search button.
    Click the reset button to reset the dates to default and requery the list box.

    So in the original query I added Between [forms]![frmMain]![txtFrom] And [forms]![frmMain]![txtTo] under one of the date columns and now the textboxes work fine in filtering for dates of that specific column. I copied and pasted it into another criteria for another date column (independent of the previous one) but now nothing shows up in the list box. How can I get around this?

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would really help to see the list box row source SQL, but maybe this will help.

    It depends on what you want.
    Do you want to see records where AAEndDate meets the date criteria AND ISExpiry meets the date criteria? (must meet both criteria)
    Or do you want to see records where AAEndDate meets the date criteria OR ISExpiry meets the date criteria? (has to meet either criteria)


    The criteria clause for the AND option would look like:
    Code:
    WHERE ([AAEndDate] Between [forms]![frmMain]![txtFrom] And [forms]![frmMain]![txtTo]) AND ( [ISExpiry] Between [forms]![frmMain]![txtFrom] And [forms]![frmMain]![txtTo]);
    The criteria clause for the OR option would look like:
    Code:
    WHERE ([AAEndDate] Between [forms]![frmMain]![txtFrom] And [forms]![frmMain]![txtTo]) OR ( [ISExpiry] Between [forms]![frmMain]![txtFrom] And [forms]![frmMain]![txtTo]);
    Note the parentheses around each criteria...


    In query design view, looking at the grid, the AND option would have the criteria for both columns on the same row.
    The OR option would have the criteria for each column on different rows.

  12. #12
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Hi there, here's the list box row source SQL:

    SELECT
    tblCompany.CompanyName, tblAssociation.Associate, tblCompany.AAEndDate, tblCompany.ISExpiry, tblCompany.ISDeclaration, tblCompany.Address, tblCompany.City, tblCompany.Province, tblCompany.PostalCode, tblCompany.ContactName, tblCompany.ContactEmail, tblCompany.ContactPhoneNumber, tblPrograms.Program

    FROM tblPrograms INNER JOIN (tblAssociation INNER JOIN tblCompany ON tblAssociation.ID = tblCompany.[Associate(s)].Value) ON tblPrograms.ID = tblCompany.Programs.Value
    WHERE (((tblCompany.CompanyName & tblAssociation.Associate) Like "*" & [forms]![frmMain]![SrchTxt] & "*"))
    ORDER BY tblCompany.CompanyName;

    I'm following the suggestions you've made and will get back to you shortly on the results. Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. Mutliselect Listbox and Date range
    By jenneedshelp in forum Access
    Replies: 1
    Last Post: 11-26-2014, 01:46 PM
  3. Date Range Criteria help
    By jsubby3 in forum Access
    Replies: 7
    Last Post: 12-03-2012, 03:14 PM
  4. Multiple Field & date range filter
    By mrkandohi001 in forum Reports
    Replies: 6
    Last Post: 01-18-2012, 03:11 AM
  5. Filter to a date range ala Allen Brown
    By kekawaka in forum Forms
    Replies: 2
    Last Post: 10-10-2011, 12:19 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums