Filtering dates

[rwest]

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

 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 & _
                    " ([eventdatestart BETWEEN #1/1/yyyy# AND #12/31/yyyy#]=" & _
                    Format(CDate(Me.FindYear), _
                           "\#m/d/yyyy\#") & ")"
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.FilterOn = (strFilter > "")
        Me.Filter = strFilter
   End If

[Mariostg]

I did not look at all your code, but as a starter, to extract the year from a date, you can do MyYear=year(MyD ate)

and I am not quite sure I understand what your intent is here:
([eventdatestart BETWEEN #1/1/yyyy# AND #12/31/yyyy#]

[sierra7]

Hi
I have created a text box called txtYear into which the user enters the Year to filter by. The After_Update event then applies the filter.

Private Sub txtYear_AfterUpdate()

'test for entry earlier than the data set
If Me.txtYear < 2000 Then
    MsgBox "Date too low"
    Exit Sub
End If

'test for entry later than current year
If Me.txtYear > Year(Date) Then
    MsgBox "Date too high"
    Exit Sub
End If

Me.Filter = "Year([EventDateStart])=" & Me.txtYear
Me.FilterOn = True

End Sub

The filtering is set at Line #15
The earlier stuff is some prefunctionary data verification.
S7

[Mariostg]

So are you saying it is working fine for you now?

[NeoPa]

@Mario - Post #3 was not submitted by the Original Poster (OP).
@RWest - Your code looks like you've taken some decent filter management code and tried to change it somewhat. You may be interested in playing with Example Filtering on a Form as it deals with dates and potentially multiple filter strings joined together depending on whether or not entries are found.

Your code doesn't seem to have multiple fields to filter on so some of the code is unnecessary. Let's look at an alternative to lines #9 to #14 (NB the current line #14 is redundent as there is only one filter field to apply) :

    If Not IsNull(Me.FindYear) Then
        strFilter = "([EventDateStart] BETWEEN #1/1/yyyy# AND #12/31/yyyy#)"
        strFilter = Replace(strFilter, "yyyy", Year(Me.FindYear))
    End If

Lines #1 & #2 are also somewhat dodgy, as the control on the form needs to remain as a date otherwise the other code won't work. Instead you may want to format it correctly as a date :

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

Use any full-date format you're comfortable with.

PS. Whenever filtering, the optimum way is generally to filter using the raw data rather than the data processed through a function. That is why I would tend to recommend your original approach (using Between two dates) rather than the apparently more straightforward way of checking the Year() of the date more directly. Both will work, but whereas the extra work checking the raw data is done once and put into the SQL string, the extra work the other way is done on every single record of the raw data. Even on those records which you're not interested in processing. Often not much of an issue with Access but can certainly prove important when moving to the types of enormous datasets you can deal with using proper BE systems (like MS-SQL; Oracle; MySQL; etc). It's a worthwhile mindset to get into. It also allows the pre-processing stage of SQL optimisation to work more efficiently.

[sierra7]

NeoPa has a point about filtering the raw data rather than processing every record to then test it. I had this in mind before posting but I tested the code and it was 'instant' so posted that option for simplicity. The original code of rwest seemed overly complicated for what he wanted to achieve.

To test the 'raw' data just change Line #14 in my previous post to

Me.Filter = "[DeliveryDate] BETWEEN #" & DateSerial(Me.txtYear, 1, 1) & " # and  #" & DateSerial(Me.txtYear, 12, 31) & "#"

I was curious to see how much the latency decreased by using the revised code. When tested on a table of 150,000 records the response times varied between 0.08 and 0.15 seconds. It seems independant of the number of records being returned. I then tested the original code (using the Year() function)and was surprised to find very little difference, which shows how forgiving Access is.

For timing purposes I usually have available three public variables dblStart, dblEnd and dblElapsed then I can plonk the following code around any operation that I want to time.e.g.

dblStart = Timer

Me.Filter = "[DeliveryDate] BETWEEN #" & DateSerial(Me.txtYear, 1, 1) & " # and  #" & DateSerial(Me.txtYear, 12, 31) & "#"
Me.FilterOn = True

dblEnd = Timer
dblElapsed = Format(dblEnd - dblStart, "0.00000") ' Elapsed time!
MsgBox "The filter took " & sngElapsed & " seconds "

If Me.Recordset.RecordCount = 0 Then MsgBox "Check a valid year has been entered", vbExclamation, "No Data"

Rather than testing that the user had entered a valid year, as in my original code I thought I would just test the record-count and use a message box to suggest there may be a problem with the input data (year) if zero records were returned.
S7

[NeoPa]

S7:

Me.Filter = "[DeliveryDate] BETWEEN #" & DateSerial(Me.txtYear, 1, 1) & " # and  #" & DateSerial(Me.txtYear, 12, 31) & "#"

I'm sorry, but this is not correct, and frustratingly, it is so often posted as a way to handle dates in SQL - particularly, but not exclusively, by experts from the other side of the pond. The format of a date literal in SQL is not dependent on the locale, but the default string that a date is converted into is. For most places outside of the USA this code will simply fail to work reliably. For more detail of when and where and why see Literal DateTimes and Their Delimiters (#). The date value should always be formatted in such a way as to be unambiguous. The SQL standard is "m/d/yyyy", just as is used in the USA, but other unambiguous formats (EG. "d mmm yyyy") work equally well in Jet SQL. The one thing that must be avoided for reliable and portable code is to rely on the default format of the current locale to format the string for you.

[sierra7]

NeoPa, I fail to see what the problem is here. DateSerial() is a function that generates a number that represents the date and has nothing to do with date literals. The "Y,M,D" order of entry of the parameters is prescribed and has nothing to do with locale.

I am based in the UK and this code works here and in my applications in France and Germany. France is a little odd when formatting 'medium' date literal output as they use four letters for some month abbreviations and three for others (e.g. janv, feb), but I digress.

If you put the following code under a command button you will see the date as either '02/08/2012' in UK or '08/02/2012' if your regional settings are English(United States)

MsgBox "Date 2nd Aug 2012 = " & DateSerial(2012, 8, 2)

What might be surprising is that you can toggle these settings while the form is still open.
S7
[imgnothumb]http://bytes.com/attachments/attachment/5905d1325842800/us.jpg[/imgnothumb]
[imgnothumb]http://bytes.com/attachments/attachment/5906d1325842800/uk.jpg[/imgnothumb]

[NeoPa]

S7:
I fail to see what the problem is here. DateSerial() is a function that generates a number that represents the date and has nothing to do with date literals.

Let me see if I can clarify then. This is all fully described in the linked article, but I appreciate how many gotchas there are in the whole subject so it never does any harm to explain in different ways - especially for someone like yourself who contributes so much of their time here helping others.

Let's start with the second statement of those quoted - "DateSerial() is a function that generates a number that represents the date and has nothing to do with date literals."
Actually, DateSerial returns a Variant (Date) value. This is important for date literals as if you assign the result of this call to a string (or even use it in an equation that builds up a string as in your code), what happens is that the VBA interpreter determines the type of the value and, realising it is best treated as a date, converts it to a string based on the settings of the current locale. Thus, when dealing with 2 Aug 2012 as in your illustrations, and in the UK where the problem is more likely to be seen, what you get from the following code is the subsequent string :

strSQL = "...WHERE [DateFld] = #" & DateSerial(2012,8,2) & "#"
...WHERE [DateFld] = #02/08/2012#

This is a problem because when SQL comes to interpret this it will treat it as 8 Feb 2012.

I do appreciate that your actual code posted, because neither of the dates can be misconstrued (The first because even when reversed is the same, and the second because the standard interpretation results in an invalid date so SQL tries again and gets 31 Dec 2012), would work perfectly in all circumstances, but the point is that it should always be done in a consistent way such that it always works and not only sometimes. The latter particularly because intermittent problems are the hardest to notice and deal with.

Does that make things clearer?