none
Access Date Filter RRS feed

  • Question

  • I Have a table with many records. at this table, there is a date field where I have the info in the format : dd/mm/yyyy hh:mm:ss

    Ex:

    01/09/2015 08:12:45, 02/09/2015 07:39:36, 03/09/2015 08:12:19, 30/09/2015 17:14:56, 30/09/2015 16:51:02.

    When i filter 'Between #01/01/2015# and #30/09/2015#, I expected recover the records between this dates, including the criteria.

    But, to my surprise, the records for the last day of month are not recovered. And for the first day returns normally.

    Why for the inferior limit of the registers came and not for the superior limit ?

    Monday, October 19, 2015 7:31 PM

Answers


  • More in my opinion, between #date1 and #date2# should include #Date2# for any time

    I don't think you fully understand the nature of the DateTime data type in Access.  It is a 64 bit floating point number whose integer part represents the days and whose fractional part represents the time of day.  Zero represents the point of time at 30 December 1899 00:00:00.  A value of date/time data type can only have a precision of 1 second, never of one day.  When you enter a date without a time of day you are actually entering an integer number, so the value represents the point of time at midnight at the start of the day in question.  It does not represent the day.

    The BETWEEN.....AND operator defines an inclusive range bounded by two values.  Consequently a range defined as BETWEEN #2015-10-01# AND #2015-10-31# will return rows with date time values on or after #2015-10-01 00:00:00# and on or before #2015-10-31 00:00:00#.  So any date/time value of #2015-10-31 00:00:01# or later will be outside the range.

    BETWEEN #2015-10-01# AND #2015-10-31# +1 is not reliable as it will return rows with a value of #2015-11-01 00:00:00# which conventionally would be regarded as outside the desired range, though the ISO standard does in fact allow midnight to be regarded as both the start and end of a day.  In most real-life situations, however, a value of #2015-11-01 00:00:00# would be regarded as the former.

    Nor is BETWEEN #2015-10-01# AND #2015-10-31 23:59:59# entirely reliable because the underlying 64 bit floating point number has a greater precision than 1 second.

    Consequently the reliable way to define a date range where the values in the column might include non-zero times of day is: [TheDate] >= #2015-10-01# AND [TheDate] <= #2015-10-31#+1.  This has long been recognized; it was recommended in the very first book on MS Access (by John L. Viescas) which I read 20 years ago.


    Ken Sheridan, Stafford, England

    Wednesday, October 21, 2015 8:44 PM
  • Consequently the reliable way to define a date range where the values in the column might include non-zero times of day is: [TheDate] >= #2015-10-01# AND [TheDate] <= #2015-10-31#+1.  This has long been recognized; it was recommended in the very first book on MS Access (by John L. Viescas) which I read 20 years ago.

    Hi Ken,

    I think for the first time in my Access-life I have to disagree with you. The second part should be "<"  and not "<=".

    So:  [TheDate] >= #2015-10-01# AND [TheDate] < #2015-10-31#+1.

    The point of time  #2015-10-31#+1 00:00:00.000 is included in the current time span, but also in the next time span.

    Imb.

    Wednesday, October 21, 2015 8:51 PM

All replies

  • Any idea ?
    Wednesday, October 21, 2015 12:12 PM
  • Try: Between #01/01/2015# And #30/09/2015 11:59 PM#
    Wednesday, October 21, 2015 12:17 PM
  • I tend to agree with DB Guy - or alternatively strip off the time component if you need to use just dates as criteria...
    Wednesday, October 21, 2015 1:27 PM
  • Why for the inferior limit of the registers came and not for the superior limit ?

    Hi André,

    For date(/time) selections I never use the BETWEEN operator, but always ">=" in combination with "<".

    Example:   MyDate >= #01/01/2015# AND MyDate < #01/10/2015#.

    This ensures a seamless sequence of time periods without any gap nor any overlap, to the sub-second level.

    Instead of the formatting I use a small (generic) function As_date, that accepts a date, and returns the completely formatted date-string in ISO format:

      MyDate >= As_date("01-jan-2015") AND MyDate < As_date("01-okt-2015")

    Imb.

    Wednesday, October 21, 2015 3:56 PM
  • Try: Between #01/01/2015# And #30/09/2015 11:59 PM#

    Hi DB Guy,

    The above still misses the seconds in the very last minute!

    See my reply to André.

    Imb.

    Wednesday, October 21, 2015 4:12 PM
  • Try: Between #01/01/2015# And #30/09/2015 11:59 PM#

    Hi DB Guy,

    The above still misses the seconds in the very last minute!

    See my reply to André.

    Imb.

    I see. So, would this help?

    Between #01/01/2015# And #30/09/2015 11:59:59 PM#

    I actually thought the second date should be in U.S. format, such as #09/30/2015 11:59:59 PM# or maybe even #9/30/2015 23:59:59#

    Wednesday, October 21, 2015 4:27 PM
  • Rather than using a BETWEEN....AND operation, restrict the results in this way:

    SELECT *
    FROM [YourTable]
    WHERE [YourDate] >= #2015-01-01#
    AND [YourDate] < #2015-10-01#;

    Note the use of the ISO standard for date notation of YYYY-MM-DD, which is internationally unambiguous.

    Or, using parameters:

    PARAMETERS [Start date:] DATETIME,
    [End date:] DATETIME;
    SELECT *
    FROM [YourTable]
    WHERE [YourDate] >= [Start date:]
    AND [YourDate] < [End date:]+1;

    In this case the 'end date' parameter would be entered in your regional short date format as 30/09/2115 when prompted at runtime.


    Ken Sheridan, Stafford, England

    Wednesday, October 21, 2015 5:09 PM
  • I actually thought the second date should be in U.S. format, such as #09/30/2015 11:59:59 PM# or maybe even #9/30/2015 23:59:59#

    Date literals must be either in US short date format, or an otherwise internationally unambiguous format, but if, as in my case, the regional format is dd/mm/yyyy, entering the date in this format in query design view is acceptable, and mandatory if the date is ambiguous (e.g. 4th July), as Access will automatically convert it in the SQL statement to US short date format.  The time can either be in 12 or 24 hour format, though most people here would use the latter.  I always use the ISO standard of YYYY-MM-DD hh:mm:ss in queries.  The standard differentiates between months and minutes by case, whereas Access uses 'm' for months and 'n' for minutes.


    Ken Sheridan, Stafford, England

    Wednesday, October 21, 2015 5:19 PM
  • Between #01/01/2015# And #30/09/2015 11:59:59 PM#

    I actually thought the second date should be in U.S. format, such as #09/30/2015 11:59:59 PM# or maybe even #9/30/2015 23:59:59#

    Hi DB guy,

    It is still that last second!

    On the accuracy of the decimal part of the dates (that is the time) I thought you could even calculate in milliseconds (even finer but then you meet the natural inaccuracy.

    But yes, the date should be in US-notation, or another unambiguous date format, e.g. the ISO-format. I always use the my own notation ("21-okt-2015"), that is converted to the ISO format, and surrounded by "#", in the As_date function.

    Imb.

    Wednesday, October 21, 2015 5:40 PM
  • Thanks by all the ansewrs,

    I will render more attention in the coming cases.

    More in my opinion, between #date1 and #date2# should include #Date2# for any time

    Wednesday, October 21, 2015 7:35 PM
  • More in my opinion, between #date1 and #date2# should include #Date2# for any time

    Hi André,

    Thus:    < (Date2 + 1)?

    Imb.

    Wednesday, October 21, 2015 7:45 PM
  • More in my opinion, between #date1 and #date2# should include #Date2# for any time

    Hi Andre,

    If you want to continue using "Between," then you can modify that code similar to what Imb posted, such as:

    Between #date1# And #date2#+1

    Cheers!

    Wednesday, October 21, 2015 7:51 PM
  • DB Guy,

    ?format(date + 1, "dd/mm/yy hh:mm:ss") = 22/10/15 00:00:00

    date1 = #20/10/2015#

    date2 = #21/10/2015#

    I´m thinking, using Between #date1# And #date2#+1 will return records of #22/10/2015 00:00:00# ?


    Wednesday, October 21, 2015 8:37 PM

  • More in my opinion, between #date1 and #date2# should include #Date2# for any time

    I don't think you fully understand the nature of the DateTime data type in Access.  It is a 64 bit floating point number whose integer part represents the days and whose fractional part represents the time of day.  Zero represents the point of time at 30 December 1899 00:00:00.  A value of date/time data type can only have a precision of 1 second, never of one day.  When you enter a date without a time of day you are actually entering an integer number, so the value represents the point of time at midnight at the start of the day in question.  It does not represent the day.

    The BETWEEN.....AND operator defines an inclusive range bounded by two values.  Consequently a range defined as BETWEEN #2015-10-01# AND #2015-10-31# will return rows with date time values on or after #2015-10-01 00:00:00# and on or before #2015-10-31 00:00:00#.  So any date/time value of #2015-10-31 00:00:01# or later will be outside the range.

    BETWEEN #2015-10-01# AND #2015-10-31# +1 is not reliable as it will return rows with a value of #2015-11-01 00:00:00# which conventionally would be regarded as outside the desired range, though the ISO standard does in fact allow midnight to be regarded as both the start and end of a day.  In most real-life situations, however, a value of #2015-11-01 00:00:00# would be regarded as the former.

    Nor is BETWEEN #2015-10-01# AND #2015-10-31 23:59:59# entirely reliable because the underlying 64 bit floating point number has a greater precision than 1 second.

    Consequently the reliable way to define a date range where the values in the column might include non-zero times of day is: [TheDate] >= #2015-10-01# AND [TheDate] <= #2015-10-31#+1.  This has long been recognized; it was recommended in the very first book on MS Access (by John L. Viescas) which I read 20 years ago.


    Ken Sheridan, Stafford, England

    Wednesday, October 21, 2015 8:44 PM
  • Between #date1# And #date2#+1

    Hi André and DB guy,

    No, I would not do the quoted above.

    "Between #date1# And #date2#+1"  includes the point of time at (date2 + 1) 00:00:00.000, in fact the start point of the next time span.

    This point of time is included in the current time span, but ALSO in the next time span, so it can be accounted for two times!

    Imb.

    Wednesday, October 21, 2015 8:44 PM
  • Consequently the reliable way to define a date range where the values in the column might include non-zero times of day is: [TheDate] >= #2015-10-01# AND [TheDate] <= #2015-10-31#+1.  This has long been recognized; it was recommended in the very first book on MS Access (by John L. Viescas) which I read 20 years ago.

    Hi Ken,

    I think for the first time in my Access-life I have to disagree with you. The second part should be "<"  and not "<=".

    So:  [TheDate] >= #2015-10-01# AND [TheDate] < #2015-10-31#+1.

    The point of time  #2015-10-31#+1 00:00:00.000 is included in the current time span, but also in the next time span.

    Imb.

    Wednesday, October 21, 2015 8:51 PM
  • I think for the first time in my Access-life I have to disagree with you. The second part should be "<"  and not "<=".

    So:  [TheDate] >= #2015-10-01# AND [TheDate] < #2015-10-31#+1.


    Mea culpa!  You are absolutely right.  That's what I meant to say.  Thanks for the catch.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, October 21, 2015 9:00 PM Typo corrected!
    Wednesday, October 21, 2015 8:59 PM
  • [TheDate] >= #2015-10-01# AND [TheDate] < #2015-10-31#+1.

    Perfect, I believe that there is nothing to add.

    Wednesday, October 21, 2015 9:24 PM
  • I really love learning from you guys. Thanks!
    Thursday, October 22, 2015 3:11 AM