If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find date
Hi,
Can anyone help with this, please? I have a date field [Arrival] which can be any date. What I'm looking to do is change this date to the Monday of the THIRD week of the month specified in [Arrival], unless this date has been passed, when I require the date to be the Monday of the THIRD week of the following month. Can it be done? dg ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#2
|
|||
|
|||
Find date
Define "third week". Is that the third Monday? The week that contains the
third Sunday? The third week that has a full seven days? -- Ken Snell MS ACCESS MVP wrote in message ... Hi, Can anyone help with this, please? I have a date field [Arrival] which can be any date. What I'm looking to do is change this date to the Monday of the THIRD week of the month specified in [Arrival], unless this date has been passed, when I require the date to be the Monday of the THIRD week of the following month. Can it be done? dg ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#3
|
|||
|
|||
Find date
On Sat, 29 May 2004 15:44:40 -0400, "Ken Snell"
wrote: Define "third week". Is that the third Monday? The week that contains the third Sunday? The third week that has a full seven days? Ken, Thanks for your interest: Third week is third Monday Ta dg ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#4
|
|||
|
|||
Find date
Here is a single expression that will return the desired "third Monday"
date: ThirdMonday = DateSerial(Year([Arrival]), Month([Arrival])-(Day([Arrival])(22-DatePart("w",DateSerial(Year([Arrival]) , Month([Arrival]),1),vbTuesday))),22-DatePart("w",DateSerial(Year([Arrival]), Month([Arrival])-(Day([Arrival])(22-DatePart("w",DateSerial(Year([Arrival]) , Month([Arrival]),1),vbTuesday))),1),vbTuesday)) -- Ken Snell MS ACCESS MVP wrote in message news On Sat, 29 May 2004 15:44:40 -0400, "Ken Snell" wrote: Define "third week". Is that the third Monday? The week that contains the third Sunday? The third week that has a full seven days? Ken, Thanks for your interest: Third week is third Monday Ta dg ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#5
|
|||
|
|||
Find date
On Sun, 30 May 2004 14:10:31 -0400, "Ken Snell"
wrote: Here is a single expression that will return the desired "third Monday" date: ThirdMonday = DateSerial(Year([Arrival]), Month([Arrival])-(Day([Arrival])(22-DatePart("w",DateSerial(Year([Arrival]) , Month([Arrival]),1),vbTuesday))),22-DatePart("w",DateSerial(Year([Arrival]), Month([Arrival])-(Day([Arrival])(22-DatePart("w",DateSerial(Year([Arrival]) , Month([Arrival]),1),vbTuesday))),1),vbTuesday)) Ken, Brilliant, thanks dg ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#6
|
|||
|
|||
Find date
Hey,
Actually the problem isnt quite hard...Access structures its dates uniquely i.e each week has a number (1-52). So just use a bunch of IF loops to check for every third week. Again, every date has a number (1 to 7) use DatePart() to check for Mondays(2). This will check the [Arrival] date. Now to check if the date has passed, use the Date() function. and voila! Job Done. |
Thread Tools | |
Display Modes | |
|
|