ACCESS FEED: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

Jumat, 25 September 2015

Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

John-

I got it. Thanks a lot.

Warm Regards,

Kevin

发自我的小米手机

在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年9月26日上午12:03写道：

Kevin-

Be aware that BETWEEN won't work if the date/time value you are comparing contains a time component. Access stores date/time values using the integer portion for the number of days since Dec 31, 1899, and it uses the fraction to store the time as a fraction of a day. As you might expect, the fractional portion for 12 noon is 0.5 - or half the day. So, for example, if you want events that occurred between January 1 and January 20, a value on January 20 after midnight will not return True using Between because any time after midnight is fractionally larger than the value for just January 20. To make sure you don't run into this, you must do:

WHERE [DateField] >= #01 Jan 2016# AND [DateField] < #21 Jan 2016#

Note that I *added* one to the end target date and used a less than comparison.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Sep 25, 2015, at 3:19 PM, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Kevin
The SQL construction you are looking for is BETWEEN … AND
For your example, you would say:
WHERE [YourDateField] between #2016-01-05# AND #2016-01-20#
There are a limited number of date formats that are permitted between the # delimiters. One is the standard American format (mm/dd/yyyy) but I prefer to use yyyy-mm-dd because it is not ambiguous.
Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, 26 September 2015 00:00
To: MS_Access_Professionals <ms_access_professionals@yahoogroups.com>
Subject: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

Dear All,

How to check if a date is among (belong to) a time interval?

For instance, how to check if 05-Jan-2016 is among "01-Jan-2016 to 20-Jan-2016". My desired output would be, if the finding is yes, returns a value "True", otherwise returns "False".

Best Regards,
Kevin

Regards,
Kevin Zhao