Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2014
    Posts
    3

    Question Unanswered: Search a date range on a sub form based on control from the main form

    I have a database containing 4 tables. I have created a main form that shows employee information with three subforms containing various information. All four tables are linked by an employee ID. I need to search a date range on one of the subforms that will filter what records show in the main form. I have found various code but have not had any luck making them work. Any help is appreciated. Thanks!

    Main Form = EmployeeData
    Subform = EDPData subform
    Main Form Data Table = EE Data
    Subform Data Table = EDPData
    Subform field to search on = Next Scheduled EDP Date

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its all down to the where clause.
    Where mydatecolumn between lowerdate and higherdate
    If you are supplying date literals (text) use iso (yyyy/mm/dd) OR us (mm/dd/yyyy)
    Delimit the date literal using the hash symbol
    #2104/02/18#

    Depending how you have set the date field you may need to fiddle with your limits. Access like many db engines stores dates AND time in the same datatype. The day runs from midnight 00:00:00 to 1 second before midnight 23:59:59. So if you have specified a time alongside the date then you need to use a different approach for the limits.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2014
    Posts
    3

    Smile Thanks

    Thanks for the reply! If there is any way possible that you could provide a code example it would truly be appreciated!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select my, column, list from mytable
    where [Next Scheduled EDP Date] between #2014/01/01# and #2014/01/31#
    or if you have got time values in your datetime column
    where [Next Scheduled EDP Date] #2014/01/01# and [Next Scheduled EDP Date] < #2014/02/01#
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2014
    Posts
    3

    Final Code

    Below is what I came up with to get the results needed. Thanks for the help!

    Private Sub Command82_Click()
    With Me.EDPData_subform.Form
    .Filter = "[Future Scheduled EDP Date] BETWEEN #" & Me.TextStart & "# AND #" & Me.TextEnd & "#"
    .FilterOn = True
    End With
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •