Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2011
    Posts
    9

    Unanswered: Filtering form by date

    Hello everyone,
    I have a form that is connected to a table called floods that has a date column as mm/dd/yyyy. I want to filter my form but only by the year. When I try to filter it nothing happens, i don't even get an error. Below is the code if anyone could help in this matter it would be greatly appreciated.
    Thanks

    Code:
    Private Sub FindYear_AfterUpdate()

    Me.FindYear = IIf(IsDate(Me.FindYear), _
    Format(Me.FindYear, " yyyy"), _
    "")


    Dim strFilter As String, strOldFilter As String

    strOldFilter = Me.Filter

    'txtFind - Date
    If Me.FindYear > "" Then _
    strFilter = strFilter & _
    " AND ([eventdatestart]=" & _
    Format(CDate(Me.FindYear), _
    "\#m/d/yyyy\#") & ")"
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
    Me.Filter = strFilter
    Me.FilterOn = (strFilter > "")
    End If
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Check what you have in "strFilter" after the line:
    Code:
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If "eventdatestart" is defined as Date/Time, filtering by year means (with 'YYYY' being the chosen year):
    Code:
    eventdatestart BETWEEN #1/1/YYYY# AND #12/31/YYYY#
    and this only if the Time part is set to '00:00:00' (or is not stored), otherwise the upper limit must be: '#12/31/YYYY 23:59:59#', or you must change the comparison to:
    Code:
    eventstart >= #1/1/YYYY# AND eventstart < #1/1/YYYY+1#
    Have a nice day!

  3. #3
    Join Date
    Dec 2011
    Posts
    9
    So i changed the eventdatestart so that it is between the first and end of the year but the code still does not work. What do you mean by Check what I have in "strFilter" after the line..?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Set a breakpoint on the line following it (F9 key), when the code halts open the debug window (Ctrl+G) then in this debug windows type:
    Code:
    debug.print strFilter
    and hit the Enter key. The value of strFilter will be printed on the next line in the debug window.
    Have a nice day!

  5. #5
    Join Date
    Dec 2011
    Posts
    9
    I did this and it highlighted this: If strFilter > "" Then
    I don't know what that means. If you could help again that would be great.
    Thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The line where to place the breakpoint is:
    Code:
    If strFilter <> strOldFilter Then
    What was displayed in the debug window?
    Have a nice day!

  7. #7
    Join Date
    Dec 2011
    Posts
    9
    nothing was displayed, it just highlights the line.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This cannot be if you opened the Debug window and typed "debug.print strFilter" into it, except if "strFilter" is an empty string.
    Have a nice day!

  9. #9
    Join Date
    Dec 2011
    Posts
    9
    Well I did what you said so I guess it is an empty string. Do you know what causes that or how I can fix it? because I do not know how that happened.
    Thanks for all your help.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the code you posted, there is a condition to be met before assigning something to strFilter:
    Code:
    If Me.FindYear > "" Then strFilter = strFilter & " AND ([eventdatestart]="  & Format(CDate(Me.FindYear), "\#m/d/yyyy\#") & ")"
    Obviously, it is not. What does Me.FindYear contain?
    Have a nice day!

Posting Permissions

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