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

Sabtu, 26 September 2015

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

Hi Kevin,

you should not be using a query to enter information -- use a form where you have control to validate.

to validate a record and prevent it from being saved, put code in the form BeforeUpdate event

here is some code you can modify:

   '----------------- make sure all required data is filled out

   'make sure SomeControlName is filled out
   If IsNull(me.SomeControlName) then

      'if it is not filled out, then move the focus to that control
      me.SomeControlName.setFocus

      'give the user a message
      msgbox "You must enter Some Data",,"Missing Data"

      'if this is a combobox, drop the list for them
      me.SomeControlName.dropDown

      'don't save the record yet
      Cancel = true

      'quit checking and give them a chance to fill it out
      exit sub
   end if

   'make sure the first Date is filled out
   If IsNull(me.[Date1]) then
      me.[Date1].setFocus
      msgbox "You must enter the first Date",,"Missing Data"
      Cancel = true
      exit sub
   end if

   'make sure the second Date is filled out
   If IsNull(me.[Date2]) then
      me.[Date2].setFocus
      msgbox "You must enter the second date",,"Missing Data"
      Cancel = true
      exit sub
   end if

   'make sure the second Date is >= Date1
   If me.[Date2] < me.[Date1] then
      me.[Date2].setFocus

      msgbox "The second date, " & me.Date2 _
        & " must be >= the first date, " _
        & me.[Date1],,"Invalid Data"

      Cancel = true

      'IF you want to undo the entries to the record
      'Me.Undo

      'IF you want to undo the entries to the field
      'Me.controlname.Undo
      Cancel = true
      exit sub
   end if

'~~~~~~~~~~~~~~~~~~~~

warm regards,
crystal

Microsoft MVP Virtual Conference 2015 -- Access Web Apps, linking to SQL Azure from Access desktop, Excel workbook, and SSMS (53 minutes)
https://channel9.msdn.com/events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps

~ have an awesome day ~

On 9/26/2015 7:21 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] wrote:

John-

How to apply this sql code to textbox in a form, or apply it to a query? Thanks.

Regards,

Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]

Date: 2015-09-26 00:02

To: MS_Access_Professionals

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

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