Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23

    Date Search fields in Form - Need help pls!!!


    Hi,

    I have a db with a form where I have to search by a date range. I have start date txt box in form and end date txt box in form. when the user enters the dates I want the form to pull data wihin that timeframe. Can someone please help? Or is there a sample db I can look at? Need to know whats the best way to do this.

    Thank you.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi, went into link above and it is not available. This is the code I have and need to include 2 text boxes start date and end date. When I open form the user can key in date range then search and only return those records between time frame. If dates are null then pull data anyway. How can I do this using this code. Before I added the date range text boxes my code worked. After I added the date range text boxes my query won't requery and I do not get lst of mbrs. I want to keep it as simple as possible. Please can you help? Thank you.

    Code:

    Private Sub command33_Click()
    lstmbr.Requery
    End Sub

    Private Sub Form_Open(Cancel as Integer)
    DoCmd.Close acForm, "frm_startup"
    End Sub

    Private Sub txtcustomer_AfterUpdate()
    lstmbr.Requery

    End Sub

    Private Sub txtstartdt_AfterUpdate()

    lstmbr.Requery
    End Sub

    Private Sub txtenddt_AfterUpdate()
    lstmbr.Requery

    End Sub

    Private Sub cmdOpenReport_Click()
    on Error Go To Err_cmdOpenReport_Click

    Dim strWhere As String
    Dim ctl As Control
    Dim varitem As Variant

    'make sure a selection has been made
    If Me.lstmbr.ItemSelected.Count = 0 Then
    MsgBox "Must select at least 1 member"
    Exit Sub
    End If

    'add selected values to string
    Set ctl=Me.lstmember
    For Each varitem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varitem) & ","

    Next varitem
    'trim trailing comma
    trWhere = Left(strWhere, Len(strWhere)-1)
    'open the report, restricted to the selected items
    DoCmd.OpenReport "rpt_fnl_cm_mbrs3", ac Preview,, "num IN(" & strWhere &")"

    Exit_cmdOpenReport_Click:
    Exit Sub

    Err_cmdOpenReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenReport_Click

    End Sub

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    OMG, Allen Browne pages I have been viewing for years are no longer working!!! All of the links on that page fail!!! This is an enormous tragedy if this is permanent.

    However, I managed to find another site that repeats Allen's article and the code. http://www.everythingaccess.com/tuto...-criteria-form. Be sure to use the horizontal scroll bar of the code window to view full lines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,070
    My guess is that it's because you are not delimiting the dates by wrapping them with #'s.
    What I don't get is how you get records from one date to another (beginning date to ending date) if you're grabbing multiple dates from a list box. How are you supposed to query BETWEEN #01/01/2017# AND #06/01/2017# if you're grabbing 3 or more dates?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi, I am confused did you respond to my request for help? I did not enter any specific dates. ex: 01/01/2017 - 06/01/2017. I just need someone to please help me how to incorporate into the code above how to query date range I am at a loss how to go about. For instance if I enter a date range of 01/01/2016 (startdate) through 06/30/2017 (end date) then in my lstmember all the data for this date range would be listed even if these fields are blank. Can someone please help? thank you.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Micron just picked dates at random for his question.

    You asked for sample code and/or an example database. Both of which are available in the last link I provided.

    But I am also confused. You posted code showing looping through a listbox then you say "in my lstmember all the data for this date range would be listed".

    You want a listbox to display data filtered by the date range? Then user selects multiple values from listbox?

    So you want a cascading (dependent) listbox - its RowSource is dependent on values selected in other control(s)?

    If this is the case then the code to modify the listbox RowSource would not be in the procedure you posted. It would be another.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    My listbox /lstmember works just fine. However, I need to add a date range. i know how to add the unbounded text boxes. What I want to accomplish is that when the form opens user enter start date and end date and that will give me a list of members within the date range. However, the date range may not have dates so if it is null I still want that member information. How can I incorporate this into the code above???

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    You don't incorporate into the posted code. Options:

    1. Set the listbox RowSource propery in form design to something like:
    SELECT fieldname FROM tablename WHERE datefield BETWEEN Nz([tbxStart], #1/1/1900#) AND Nz([tbxEnd], #12/31/2900#);

    2. Another VBA procedure that sets the listbox RowSource property. Something like:
    Code:
    Sub SetListbox()
    If Not IsNull(Me.tbxStart) And Not IsNull(Me.tbxEnd) Then
        strWhere = " [datefield] BETWEEN #" & Me.tbxStart & "# AND #" & Me.tbxEnd & "#"
    End If
    Me.lstmbr.RowSource = "SELECT fieldname FROM tablename" & strWhere & ";"
    End Sub
    Then call that procedure from tbxStart and tbxEnd AfterUpdate events as well as the form Current event.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi how can I update this code to include a button to be able to select all lines in a listbox (lstmbr)???

    Private Sub command33_Click()
    lstmbr.Requery
    End Sub

    Private Sub Form_Open(Cancel as Integer)
    DoCmd.Close acForm, "frm_startup"
    End Sub

    Private Sub txtcustomer_AfterUpdate()
    lstmbr.Requery

    End Sub

    Private Sub txtstartdt_AfterUpdate()

    lstmbr.Requery
    End Sub

    Private Sub txtenddt_AfterUpdate()
    lstmbr.Requery

    End Sub

    Private Sub cmdOpenReport_Click()
    on Error Go To Err_cmdOpenReport_Click

    Dim strWhere As String
    Dim ctl As Control
    Dim varitem As Variant

    'make sure a selection has been made
    If Me.lstmbr.ItemSelected.Count = 0 Then
    MsgBox "Must select at least 1 member"
    Exit Sub
    End If

    'add selected values to string
    Set ctl=Me.lstmember
    For Each varitem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varitem) & ","

    Next varitem
    'trim trailing comma
    trWhere = Left(strWhere, Len(strWhere)-1)
    'open the report, restricted to the selected items
    DoCmd.OpenReport "rpt_fnl_cm_mbrs3", ac Preview,, "num IN(" & strWhere &")"

    Exit_cmdOpenReport_Click:
    Exit Sub

    Err_cmdOpenReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenReport_Click

    End Sub

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Perhaps in command33_Click - to automatically select all lines in a listbox:

    Code:
    Private Sub command33_Click()
    Dim x As Integer
    For x = 0 To Me.lstmbr.ListCount
        Me.lstmbr.Selected(x) = True
    Next
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi thank you your prompt response is very much appreciated. I tested the code above for select all and that works great. However, I need the option to select all or just the ones I highlight. The code above only selects all and I can't undo the select all. Do I need to buttons one for select all and one one the ones I highlight or can you incorporate everything into one button. Thank you for all your help.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Separate buttons.

    Command Button to select all.

    Another to unselect all.

    Or a single Toggle Button to select/unselect. Set the Caption property in design to "Select All" then code like:
    Code:
    Private Sub Toggle0_Click()
    Dim x As Integer
    Me.Toggle0.Caption = IIf(Me.Toggle, "Unselect All", "Select All")
    For x = 0 To Me.lstmbr.ListCount
        Me.lstmbr.Selected(x) = Me.Toggle0
    Next
    End Sub
    A Command Button to open report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Nyrubi is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    23
    Hi tried using the toggle button but I am getting error message. Run-time error 438: Object doesn't support this property or method. How can this be fixed??? Thank you

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Post your attempted code.

    What line triggers the error?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search form with date
    By Delta729 in forum Forms
    Replies: 4
    Last Post: 07-14-2015, 04:56 PM
  2. Replies: 4
    Last Post: 04-21-2015, 07:12 PM
  3. Replies: 9
    Last Post: 11-25-2013, 04:33 PM
  4. Display Fields in Search Form
    By j2curtis64 in forum Forms
    Replies: 1
    Last Post: 07-18-2011, 01:37 PM
  5. Search form with a date range
    By mantro174 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:45 PM

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