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

John-

Don't know why. I uploaded the new file with the code from your last email. It shows #error. Please help.

Regards,

Kevin

Regards,

Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-27 22:27
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

It doesn't do that in the last database you uploaded.

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 27, 2015, at 4:22 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
It returns #error as always. Help Please.
Regards,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-27 22:10
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

Kevin-

You'll see #Error when there is not data to compare. Try this:

=IIf([Form].[NewRecord],0,DLookUp("twinRate","tblRateAgreement","ItineraryID=" & Nz([ItineraryID],0) & " AND [Checkin] > #" & Nz([Forms]![frmHotel]![frmRateAgreement].[Form].[from],0) & "# AND [Checkin] < #" & Nz([Forms]![frmHotel]![frmRateAgreement].[Form].[to],9999) & "#"))

If the outer form is on a new (empty) record, it will return 0 rather than call the DLookup.

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 27, 2015, at 2:09 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I uploaded the file corrected to group, it still appears error, please check the file uploaded, and help.
Regards,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-27 19:42
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

Kevin-

The expression that works is:

=DLookUp("twinRate","tblRateAggreement","ItineraryID=" & Nz([ItineraryID],0) & " AND [Checkin] > #" & [Forms]![frmHotel]![frmRateAgreement].[Form].[from] & "# AND [Checkin] < #" & [Forms]![frmHotel]![frmRateAgreement].[Form].[to] & "#")

But your form makes no sense. You have ItineraryID in both tables, but the subform isn't linked on that value. I would expect to see ItineraryID in the Link Master Fields and Link Child Fields properties of the subform.

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 27, 2015, at 12:05 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I just uploaded the file: /files/2_AssistanceNeeded/CheckDates.accdb. Please help.
Regards,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-27 16:04
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

Kevin-

Don't use the WHERE keyword in the third argument of a Domain function. Are "from" and "to" names of controls on your form? If so, then it should look like:

=DLookup("twinRate","tblItinerary","ItineraryID=" & ItineraryID * " AND [Checkin] > #" & [from] & "# & [Checkin] < #" & [to] & "#")

Note that I added the hashtag delimiters around the date literals.

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 27, 2015, at 8:26 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Crystal-
In an unbound textbox, I did someting like this: =DLookup("twinRate","tblItinerary","ItineraryID=" & ItineraryID AND Where [Checkin] > "from" & [Checkin] < "to") , then pops up a window showing "the expression you entered contains invalid syntax. What could be wrong?
Regards,
Kevin

Regards,
Kevin Zhao

From: crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]
Date: 2015-09-27 13:43
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?

Hi Kevin,

you are right -- what I wrote does not check to see if a date is between something else ...

use the information that John gave you and modify the code :) ~ sorry, but telling you what to write won't really help because, if you can't do it, you won't be able to support it.

Get some sleep and try it tomorrow morning when you are fresh :) ~ you have everything you need except the right test in the right place

post what you come up with and we can help you fix it if you can't get it to work on your own.

warm regards,
crystal

~ have an awesome day ~

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

Crystal-

I understand the code you wrote, but it didn't show the desired result for checking if a date is in between of two dates interval. I discribe my situation again as below. Please help:

I have three text fields in a form like this:

txtDateSearch (date format)

txtBlackoutDateStart (date format)

txtBlackoutDateEnd (date format)

txtRate (text format)

I want to check if txtDateSearch is in between txtBlackoutDateStart and txtBlackoutDateEnd , if the result is "yes", then the txtRate displays "No offer for blackout dates", otherwise it displys "Normal rates applied". Can this be done? Thanks in advance.

Regards,

Kevin

Regards,
Kevin Zhao

From: crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]

Date: 2015-09-27 12:08

To: MS_Access_Professionals

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

Hi Kevin,

It is not hard to learn the basics of VBA ... it makes sense. The 3 chapters (all I wrote) posted here will not take you long to read. Chapter 1 ... maybe 20 minutes. Chapters 2 and 3 you can read for logic but not memorize -- they are there for reference and foundation.

Learn VBA
http://www.AccessMVP.com/strive4peace/VBA.htm

this should give you the knowledge you need to read basic code.

Allocate about a minute/line to read the code with comprehension AFTER you have read the above chapters . If you give it time when you are relaxed and open to learn, you will understand the code.

You need to understand what to do so you can change the code. After all, you will be supporting it so you have to know what it does.

warm regards,
crystal

~ have an awesome day ~

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

Crystal-

My problem is that I don't know how to use these codes in forms. For example. If I have three text fields in a form like this:

txtDateSearch (date format)

txtBlackoutDateStart (date format)

txtBlackoutDateEnd (date format)

txtRate (text format)

I want to check if txtDateSearch is in between txtBlackoutDateStart and txtBlackoutDateEnd , if the result is "yes", then the txtRate displays "No offer for blackout dates", otherwise it displys "Normal rates applied". Can this be done? Thanks in advance.

Regards,

Kevin

Regards,
Kevin Zhao

From: crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]

Date: 2015-09-27 11:17

To: MS_Access_Professionals

Subject: 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