Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2009
    Posts
    18

    Unanswered: Date Range in Subform not filtering

    I have been trying to get this working for 2 days with no success. I have searched google and this forum, and have tried to many things to list. I have Access 2007.
    I have a subform within a form that I want to filter by date range. In the main form I have two textboxes (sdate and edate, set to shortdate same as table) and a cmd button. Within the main form I have the subform with the date [entrydate]. I've tried many different variations of
    Me!Form.Filter Between [Forms]![Formname]![control] AND [Forms]![Formname]![control]
    with no success. My result is the subform shows all records, then when I press the cmd button nothing happens. No errors, yet nothing is filtered.
    I've tried first sending focus to the subform before applying the filter with SubformName.SetFocus and I've tried Refreshing the subform after the filter with Me.Refresh.
    I have the same filter using another form and the criteria in a query for printing reports and it works fine, but I need to also be able to find a record by date to edit. The forms and queries are different between the form/subform I am trying to use and the report, so they shouldn't conflict in any way. Any help is greatly appreciated.
    Mike

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    me.filter = "mycolumnname between [Forms]![Formname]![control1] AND [Forms]![Formname]![control2]"
    me.filteron=true
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Correct me if I'm wrong healdem, but don't you need the #'s in there for a date?

    Code:
    me.filter = "mycolumnname between " & "#" & "[Forms]![Formname]![control1] AND [Forms]![Formname]![control2]" & "#"
    me.filteron=true
    ?
    Me.Geek = True

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by nckdryr
    Correct me if I'm wrong healdem, but don't you need the #'s in there for a date?

    Code:
    me.filter = "mycolumnname between " & "#" & "[Forms]![Formname]![control1] AND [Forms]![Formname]![control2]" & "#"
    me.filteron=true
    ?
    you are probably right, I'd forgotten the original request was dates
    you may be better off with
    Code:
    me.filter = "mycolumnname between #" & format([Forms]![Formname]![control1],"YYYY/MM/DD") & "# AND #" & format([Forms]![Formname]![control2],"YYYY/MM/DD") & "#"
    me.filteron=true
    of course you may need to specify a US date ie format (blah,"MM/DD/YYYY")
    as opposed to a true date DD/MM/YYYY or an ISO date YYYY/MM/DD

    you may also need to add one to the end date if you have used NOW() instead of date to set the datevalue, as the between range will cut off any dates with times.

    ie
    where mydatevalue < 2009/03/17
    will return any rows which mydatevalue was before 17th march. if your users want to use that as a date bracket (ie upto and including the 17th of march then you probably need to consider
    '....where mydatevalue <' DateAdd("d",1,"2009/03/17")
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    18
    Alright now I'm getting somewhere. I had the begin/end textboxes and cmd button in the header of the main form. When I enter valid dates and click the button, still nothing happens.
    Me.Filter = "entrydate between #" & Format([Forms]![selectTradeBySymbolForm]![sdate], "MM/DD/YYYY") & "# AND #" & Format([Forms]![selectTradeBySymbolForm]![edate], "MM/DD/YYYY") & "#"
    Me.FilterOn = True
    I moved both textboxes and button to subform header, but when I click the button I get a run-time error 2450 "can't find the form 'selectTradeBySymbolSubform' referred to in a macro expression or visual basic code." This is the subform that the textboxes and button is in. I have checked for typos.
    Me.Filter = "entrydate between #" & Format([Forms]![selectTradeBySymbolSubform]![sdate], "MM/DD/YYYY") & "# AND #" & Format([Forms]![selectTradeBySymbolSubform]![edate], "MM/DD/YYYY") & "#"
    Me.FilterOn = True
    I moved the button to the main form header and left the textboxes in the subform header and it filters correctly.
    Me.Filter = "entrydate between #" & Format([Forms]![selectTradeBySymbolForm]![sdate], "MM/DD/YYYY") & "# AND #" & Format([Forms]![selectTradeBySymbolForm]![edate], "MM/DD/YYYY") & "#"
    Me.FilterOn = True


    selectTradeBySymbolForm - the main form
    selectTradeBySymbolSubform - the subform within the main form
    entrydate - the date that I'm trying to filter
    sdate - the unbound begin date textbox
    edate - the unbound end date textbox

    I can use it like this, but I'd rather have the textboxes and cmd button together if possible. Does anything look wrong with my code, or is there anything else I should check? If not, that's fine. Thank you for your help, you've saved me another day of headaches.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Have a look at referring to subform/form controls, it's a handy reference I keep bookmarked. If you keep the controls in the main form header, I think you should be able to do like (I code specifically when in doubt, i.e. eliminate the "me."s):

    Forms!selectTradeBySymbolForm.Filter = "entrydate between #" & Format(Forms!selectTradeBySymbolForm!sdate, "MM/DD/YYYY") & "# AND #" & Format(Forms!selectTradeBySymbolForm!edate, "MM/DD/YYYY") & "#"
    Forms!selectTradeBySymbolForm.FilterOn = True

    If that doesn't work, try a requery after the filteron.

    EDIT:
    And if that doesn't work, try stepping through the code to make sure that it is indeed finding the controls. Speaking of which, you may want to validate the date textboxes to make sure there is a date there in the first place and that it's a valid date.
    Me.Geek = True

  7. #7
    Join Date
    Mar 2009
    Posts
    18
    Ok well I've been doing some more google searching and experimenting, and the only way I can get the filter to work is when the button and code is on the subform, but the controls are on the main form. I've tried changing to
    Me.Filter = "entrydate between #" & Format([Forms]![Me.selectTradeBySymbolForm]![sdate], "MM/DD/YYYY") & "# AND #" & Format([Forms]![Me.selectTradeBySymbolForm]![edate], "MM/DD/YYYY") & "#"
    to
    Forms!selectTradeBySymbolForm.Filter = "entrydate between #" & Format(Me!sdate, "MM/DD/YYYY") & "# AND #" & Format(Me!edate, "MM/DD/YYYY") & "#"
    whenever I try to refer to a control in the current form or subform, I get the run-time error 2450 again. Any more ideas?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to refer to a control or variable in another form
    forms!myotherformname!mycontolname

    I'd suggest you assing the value of the filter to a variable
    strFilter = "entrydate between #" & Format(Me!sdate, "MM/DD/YYYY") & "# AND #" & Format(Me!edate, "MM/DD/YYYY") & "#"
    Forms!selectTradeBySymbolForm.Filter = strFilter

    you need to prove twhere the problem is
    it could be bad data
    it could be bad SQL
    it could be a whole raft of things.

    check that strFlter is valid SQL

    effectivley a filter is a where clause
    so I'd suggest you make sure it 'looks' right, put a watch on the strFilter=.....
    then run a query that uses that same where clause
    eg select * from mytable where ....... add the contents of your strFilter
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Also, are you applying the filter to the mainform or the subform? If you use the "me." method it's whereever teh control is that the event is firing in, hence why I suggest explicitly referencing the property rather than "me." when trouble-shooting.
    Me.Geek = True

  10. #10
    Join Date
    Mar 2009
    Posts
    18
    Well I've gotten another step closer, It is recognizing the subform now, though still not filtering. I know that it is recognizing it because I can see the screen flash when it refreshes, and also I'm not getting the error. I have fixed this issue by calling the mainform.form instead of directly calling the subform, like so:
    Forms!Mainform.Form.Filter
    NOT
    Forms!Subform.Filter

    Now if I could just get it to filter. I have checked and double checked, and all the date boxes including the unbound boxes and the bound control I am trying to search are all formatted as short date. I am entering a valid date into the textboxes. I have tried assigning the value to a variable, but that has not worked for me either.
    Dim strFilter As Variant
    strFilter = "entrydate between #" & Format(Me!sdate, "MM/DD/YYYY") & "# AND #" & Format(Me!edate, "MM/DD/YYYY") & "#"
    Forms!selectTradeBySymbolForm.Form.Filter = strFilter
    I do think it is a good idea to validate the date as valid, the best way I guess would be a msgbox. I'll probably do something like
    If IsDate([sdate] AND [edate]) Then
    'Filter...
    Else
    MsgBox "Invalid Date!"
    But I'd like to get the filter working first. Everything I know is self taught, and to keep from turning things into a mess I focus on one thing at a time so that if something doesn't work, I know I can go back to my original working code and I can more easily troubleshoot the problem.

    EDIT: I am applying the filter to the subform from the main form.

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by L3Tum
    [INDENT]
    Dim strFilter As Variant
    strFilter = "entrydate between #" & Format(Me!sdate, "MM/DD/YYYY") & "# AND #" & Format(Me!edate, "MM/DD/YYYY") & "#"
    Forms!selectTradeBySymbolForm.Form.Filter = strFilter
    ...
    EDIT: I am applying the filter to the subform from the main form.
    Actually, you're not. At least not with that code. You're still applying the filter to the main form, where you need to be applying it to the subform. Try like:
    Forms!selectTradeBySymbolForm!selectTradeBySymbolS ubform.Form.Filter = strFilter

    Also, don't forget to set the FilterOn property in order to actually apply the filter:
    Forms!selectTradeBySymbolForm!selectTradeBySymbolS ubform.Form.FilterOn = True
    Me.Geek = True

  12. #12
    Join Date
    Mar 2009
    Posts
    18
    Well I got the filter working, thanks everyone for their help, it is greatly appreciated! I added in some code to make sure the date entered is valid, and then pop a msgbox if not, but it always pops even when the date is valid. The message box pops up and when I click OK the form filters correctly. I get no vb or access errors. I am not using a Now() button, but I am using a Date() button to enter some of the dates. That may have something to do with it. I would think that access stores the date the same though, only the print format changes. Or it may just be a rookie mistake in my IsDate code somewhere? Also, I know there are probably better ways to check the date instead of a nested If statement, but I tried using one If and putting an AND between the two textboxes many different ways with no luck, so this is what I ended up with. Here is the code:
    Check sdate and edate for valid date format. If valid, run filter.
    Private Sub cmdFilter_Click()
    Dim strFilter As Variant

    sdate.SetFocus 'set focus to sdate to be checked
    If IsDate(Forms!selectTradeBySymbolForm.sdate.Text) = True Then 'check sdate for valid date
    edate.SetFocus 'set focus to edate to be checked
    If IsDate(Forms!selectTradeBySymbolForm.edate.Text) = True Then 'check edate for valid date
    'if both sdate and edate are valid, run filter
    strFilter = "entrydate between #" & Format(Me!sdate, "MM/DD/YYYY") & "# AND #" & Format(Me!edate, "MM/DD/YYYY") & "#"
    Forms!selectTradeBySymbolForm!selectTradeBySymbolS ubform.Form.Filter = strFilter
    Forms!selectTradeBySymbolForm!selectTradeBySymbolS ubform.Form.FilterOn = True
    Else: GoTo Invalid_Date 'edate not valid
    End If
    Else: GoTo Invalid_Date 'sdate not valid
    End If

    Invalid_Date: 'One or both dates are invalid. Popup msgbox and set focus back to sdate....
    If MsgBox("Invalid Date Format. Please enter date as MM/DD/YYYY.", vbOKOnly + vbExclamation, "Invalid Date") = vbOK Then
    sdate.SetFocus
    Else
    sdate.SetFocus
    End If

    End Sub
    Note that I had to enter the SetFocus' in there before the If IsDate's because I kept getting a vb error that the control was not in focus.



    For anyone else who is having a problem with subform date range filtering, here is the final code format:
    strFilter = "dateControlToBeFiltered between #" & Format(Me!dateBegin, "MM/DD/YYYY") & "# AND #" & Format(Me!dateEnd, "MM/DD/YYYY") & "#"
    Forms!nameOfMainForm!nameOfSubform.Form.Filter = strFilter
    Forms!nameOfMainForm!nameOfSubform.Form.FilterOn = True

  13. #13
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by L3Tum
    Check sdate and edate for valid date format. If valid, run filter.
    Private Sub cmdFilter_Click()
    Dim strFilter As Variant

    sdate.SetFocus 'set focus to sdate to be checked
    If IsDate(Forms!selectTradeBySymbolForm.sdate.Text) = True Then 'check sdate for valid date
    edate.SetFocus 'set focus to edate to be checked
    If IsDate(Forms!selectTradeBySymbolForm.edate.Text) = True Then 'check edate for valid date
    'if both sdate and edate are valid, run filter
    strFilter = "entrydate between #" & Format(Me!sdate, "MM/DD/YYYY") & "# AND #" & Format(Me!edate, "MM/DD/YYYY") & "#"
    Forms!selectTradeBySymbolForm!selectTradeBySymbolS ubform.Form.Filter = strFilter
    Forms!selectTradeBySymbolForm!selectTradeBySymbolS ubform.Form.FilterOn = True
    Else: GoTo Invalid_Date 'edate not valid
    End If
    Else: GoTo Invalid_Date 'sdate not valid
    End If

    Invalid_Date: 'One or both dates are invalid. Popup msgbox and set focus back to sdate....
    If MsgBox("Invalid Date Format. Please enter date as MM/DD/YYYY.", vbOKOnly + vbExclamation, "Invalid Date") = vbOK Then
    sdate.SetFocus
    Else
    sdate.SetFocus
    End If

    End Sub
    You're very close. The problem is that when Access goes into a module, it doesn't stop until it either sees an "End Sub"/"End Function"/"Exit Sub"/"Exit Function" statement or an error occurs without proper error handling. So after your code runs through the top part of your code it just keeps on running through into the next part since there's nothing to tell it not to. This is why, for example, ErrorHandling procedures are typically put after an Exit Sub / Exit Function statement, so that the only way for the code to get to it is if an error would occur. A very useful exercise I would recommend for you is to put a stop in the code (click the gray bar to the left of the first executable line in the sub, you'll get a little red stop sign on that line), and then run the code; it should stop the code and go into debugger mode. Then you can use F8 to step through the code line by line. It's VERY useful for understanding your code and figuring out why it's not behaving the way it should. (Plus you can also do handy things like mousing over a variable name to see it's current value) To fix it, I would just tweak your code to be:
    Code:
    'Check sdate and edate for valid date format. If valid, run filter.
    Private Sub cmdFilter_Click()
        
        Dim strFilter As Variant
        
        sdate.SetFocus 'set focus to sdate to be checked
        If IsDate(Forms!selectTradeBySymbolForm.sdate.Text) = True Then 'check sdate for valid date
            EDate.SetFocus 'set focus to edate to be checked
            If IsDate(Forms!selectTradeBySymbolForm.EDate.Text) = True Then 'check edate for valid date
                'if both sdate and edate are valid, run filter
                strFilter = "entrydate between #" & Format(Me!sdate, "MM/DD/YYYY") & "# AND #" & Format(Me!EDate, "MM/DD/YYYY") & "#"
                Forms!selectTradeBySymbolForm!selectTradeBySymbolSubform.Form.Filter = strFilter
                Forms!selectTradeBySymbolForm!selectTradeBySymbolSubform.Form.FilterOn = True
            Else
                GoTo Invalid_Date 'edate not valid
            End If
        Else
            GoTo Invalid_Date 'sdate not valid
        End If
    
        Exit Sub
    
    Invalid_Date:     'One or both dates are invalid. Popup msgbox and set focus back to sdate....
        MsgBox "Invalid Date Format. Please enter date as MM/DD/YYYY.", _
                vbOKOnly + vbExclamation, _
                "Invalid Date"
        sdate.SetFocus
        Exit Sub
        
    End Sub
    Or if you really want to make your users happy and actually tell them which box is screwing up, do like:
    Code:
    'Check sdate and edate for valid date format. If valid, run filter.
    Private Sub cmdFilter_Click()
        
        Dim strFilter As Variant, _
            strSDate As String, _
            strEDate As String
        
        'check sdate
        sdate.SetFocus 'set focus to sdate to be checked
        strSDate = Forms!selectTradeBySymbolForm.sdate.Text
        If Not IsDate(strSDate) Then
            MsgBox "Invalid Date Format '" & strSDate & "'." & vbNewLine & _
                        "Please enter start date as MM/DD/YYYY.", _
                    vbExclamation, _
                    "Invalid Date"
            GoTo ExitSub
        End If
        
        'check edate
        EDate.SetFocus 'set focus to edate to be checked
        strEDate = Forms!selectTradeBySymbolForm.EDate.Text
        If Not IsDate(strEDate) Then
            MsgBox "Invalid Date Format '" & strEDate & "'." & vbNewLine & _
                        "Please enter start date as MM/DD/YYYY.", _
                    vbExclamation, _
                    "Invalid Date"
            GoTo ExitSub
        End If
        
        'if both sdate and edate are valid, run filter
        strFilter = "entrydate BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                    "# AND #" & Format(strEDate, "MM/DD/YYYY") & "#"
        Forms!selectTradeBySymbolForm!selectTradeBySymbolSubform.Form.Filter = strFilter
        Forms!selectTradeBySymbolForm!selectTradeBySymbolSubform.Form.FilterOn = True
        
    ExitSub:
        
    End Sub
    I think you're getting a handle on how Access deals with dates/times, but if you'd like to learn more, I'd suggest this article.
    Me.Geek = True

  14. #14
    Join Date
    Mar 2009
    Posts
    18
    Thank you for that, very explanatory. I find it easier to learn when I actually know why I am doing something, not just how to do it.

    When I tried to test the new code by entering invalid dates, I get an access error before I can even click the filter button. As soon as I leave the field it pops up. I did some more searching and tried things like: (2113 is the error I'm getting)
    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    Select Case DataErr
        Case 2113
            On Error Resume Next
            Response = acDataErrContinue
            Case Else
                MsgBox "The form error, " & DataErr & " has occurred.", vbOKOnly, "Error"
                Response = acDataErrDisplay
        End Select
    
    End Sub
    I tried this a few different ways, but all it does is stop the error box from popping up, yet I cannot change focus to the other field or the button until I either enter a valid date or leave the field empty. I tried looking for another way to disable errors or warning in the settings but couldn't seem to find one. I also read about DoCmd.SetWarnings False but that did not seem to work either. I may remove the msgboxes from the button and move them to the case response in the Form_Error, or try checking the dates in an AfterUpdate for each textbox. Not sure right now, my eyes are getting tired though so I'll have to look into it again tomorrow, but thanks again for your time and patience with me.

Posting Permissions

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