Kevin-
I was afraid of that. The solution will be to put that expression as an assignment to an unbound (no Control Source) text box in both the form Current event and the After Update event of the CheckIn date.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Sep 28, 2015, at 1:02 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John-After adding me. refresh, twinRate value didn't display according to the date interval. It seems Dlookup on search for the first record. What could be wrong?Regards,Kevin
Regards,Kevin ZhaoDate: 2015-09-28 00:01Subject: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?Kevin-
If the check-in date changes, you have to Refresh the form to get the calculated DLookup to recalculate using the new date.Private Sub CheckIn_AfterUpdate()Me.RefreshEnd SubJohn Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Sep 27, 2015, at 5:26 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John-Someting strange happened, after changing the Checkin to a new date which belongs to another time interval, it ttakes long time to load the twinRate, and it keeps displaying the value from first record instead of the desired one. I uploaded the latest file to group. Please help.Regards,Kevin
Regards,Kevin ZhaoDate: 2015-09-27 23:14Subject: Re: [MS_AccessPros] How to check if a date is among (belong to) a time interval?Kevin-
You can change back to Chinese settings if you do this:=IIf([Form].[NewRecord],0,DLookUp("twinRate","tblRateAgreement","ItineraryID=" & Nz([ItineraryID],0) & " AND [Checkin] > #" & Format(Nz([Forms]![frmHotel]![frmRateAgreement].[Form].[from],0), "yyyy-mm-dd") & "# AND [Checkin] < #" & Format(Nz([Forms]![frmHotel]![frmRateAgreement].[Form].[to],9999), "yyyy-mm-dd") & "#"))John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Sep 27, 2015, at 5:10 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John-I got it. It was caused by different time format. The date on my computer is Chinese style. After changing it to English style, it worked. Is it possible to do something with the code to avoid #error due to different time setting? Thanks.Regards,Kevin
Regards,Kevin ZhaoDate: 2015-09-27 22:27Subject: 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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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 ZhaoDate: 2015-09-27 22:10Subject: 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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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 ZhaoDate: 2015-09-27 19:42Subject: 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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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 ZhaoDate: 2015-09-27 16:04Subject: 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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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 ZhaoDate: 2015-09-27 13:43Subject: 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 ZhaoDate: 2015-09-27 12:08Subject: 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 ZhaoFrom: crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]Date: 2015-09-27 11:17Subject: 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 ZhaoDate: 2015-09-26 00:02Subject: 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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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,
GrahamFrom: 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,KevinRegards,Kevin Zhao
Posted by: zhaoliqingoffice <zhaoliqingoffice@163.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (32) |
Tidak ada komentar:
Posting Komentar