473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Filtering dates

10
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

Expand|Select|Wrap|Line Numbers
  1.  Me.FindYear = IIf(IsDate(Me.FindYear), _
  2.                                  Format(Me.FindYear, " yyyy"), "")
  3.  
  4.     Dim strFilter As String, strOldFilter As String
  5.  
  6.     strOldFilter = Me.Filter
  7.  
  8.     'txtFind - Date
  9.     If Me.FindYear > "" Then _
  10.         strFilter = strFilter & _
  11.                     " ([eventdatestart BETWEEN #1/1/yyyy# AND #12/31/yyyy#]=" & _
  12.                     Format(CDate(Me.FindYear), _
  13.                            "\#m/d/yyyy\#") & ")"
  14.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  15.     If strFilter <> strOldFilter Then
  16.         Me.FilterOn = (strFilter > "")
  17.         Me.Filter = strFilter
  18.    End If 
  19.  
Jan 4 '12 #1
8 2668
Mariostg
332 100+
I did not look at all your code, but as a starter, to extract the year from a date, you can do MyYear=year(MyDate)

and I am not quite sure I understand what your intent is here:
([eventdatestart BETWEEN #1/1/yyyy# AND #12/31/yyyy#]
Jan 4 '12 #2
sierra7
446 Expert 256MB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtYear_AfterUpdate()
  2.  
  3. 'test for entry earlier than the data set
  4. If Me.txtYear < 2000 Then
  5.     MsgBox "Date too low"
  6.     Exit Sub
  7. End If
  8.  
  9. 'test for entry later than current year
  10. If Me.txtYear > Year(Date) Then
  11.     MsgBox "Date too high"
  12.     Exit Sub
  13. End If
  14.  
  15. Me.Filter = "Year([EventDateStart])=" & Me.txtYear
  16. Me.FilterOn = True
  17.  
  18. End Sub
  19.  
The filtering is set at Line #15
The earlier stuff is some prefunctionary data verification.
S7
Jan 4 '12 #3
Mariostg
332 100+
So are you saying it is working fine for you now?
Jan 4 '12 #4
NeoPa
32,556 Expert Mod 16PB
@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) :
Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(Me.FindYear) Then
  2.         strFilter = "([EventDateStart] BETWEEN #1/1/yyyy# AND #12/31/yyyy#)"
  3.         strFilter = Replace(strFilter, "yyyy", Year(Me.FindYear))
  4.     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 :
Expand|Select|Wrap|Line Numbers
  1.     Me.FindYear = IIf(IsDate(Me.FindYear), _
  2.                       Format(Me.FindYear, "mmm d, yyyy"), _
  3.                       "")
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.
Jan 5 '12 #5
sierra7
446 Expert 256MB
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
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[DeliveryDate] BETWEEN #" & DateSerial(Me.txtYear, 1, 1) & " # and  #" & DateSerial(Me.txtYear, 12, 31) & "#"
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. dblStart = Timer
  2.  
  3. Me.Filter = "[DeliveryDate] BETWEEN #" & DateSerial(Me.txtYear, 1, 1) & " # and  #" & DateSerial(Me.txtYear, 12, 31) & "#"
  4. Me.FilterOn = True
  5.  
  6. dblEnd = Timer
  7. dblElapsed = Format(dblEnd - dblStart, "0.00000") ' Elapsed time!
  8. MsgBox "The filter took " & sngElapsed & " seconds "
  9.  
  10. 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
Jan 5 '12 #6
NeoPa
32,556 Expert Mod 16PB
S7:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 6 '12 #7
sierra7
446 Expert 256MB
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)
Expand|Select|Wrap|Line Numbers
  1. 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


Attached Images
File Type: jpg US.jpg (7.0 KB, 511 views)
File Type: jpg UK.jpg (7.2 KB, 521 views)
Jan 6 '12 #8
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "...WHERE [DateFld] = #" & DateSerial(2012,8,2) & "#"
  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?
Jan 6 '12 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Peter Timbol | last post by:
how can i filter dates in vb6 and msaccess 2000.. im using adodc my code is pickfrom and pickto are datetime picker controls.. dtr is my table Adodc3.CommandType = adCmdText...
3
by: iamguyster | last post by:
Hi, I have an exercise I need to give to my pupils (I'm a teacher!) and I am trying to get a query working preferably using the query design view, without having to edit the SQL. The query involves...
9
by: Anneybo | last post by:
Alright, I give up! I'm asking the experts. I have created a database that calculates PTO for employees. I need to be able to cache the report by user entered dates and specific employee names. I...
5
by: pinpe | last post by:
Hi, I have problem of filtering a log file from my perl script. This is the file content of the file pinpe.csv: 2009-06-16 2009-01-29 2009-06-02 2008-03-05
18
by: mlcampeau | last post by:
I have a lengthy query that I am now trying to filter. The query calculates an employee's Anniversary Date in which they are eligible for the next level of Annual Vacation. (i.e. For 1-6 years of...
2
by: ccwells | last post by:
Hi, I am a novice to VB, using Access 2003 on a XP-SP2 platform, and I have a small database for tracking expenses, and want to be able to filter my expense report by date. I currently have a...
2
by: Big X | last post by:
Hi, I am having a little trouble with some data I have been sent. Seems they want me to remove all the date that have a 5 year expiry date. I receive the data in csv file so the properties of the...
9
by: sparks | last post by:
Right now I had to build a report that allowed the people to check for gross outliers in their data input. short I am looking at 2.5* std dev + - anyway I used 2 dummy variables in the query the...
2
by: poteatc | last post by:
I want to filter my reports according date and time. I am already successful with filtering dates and times, but not overnight. I want to filter according to a shift that starts for example,...
1
by: tracy6001 | last post by:
hi, i am trying to fetch three fields from my table according to the two dates which will be given by the user in two text boxes in my form.. i am facing a problem.. my dates in the database is in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.