Find date

  • Thread starter Thread starter VillageIdiot
  • Start date Start date
V

VillageIdiot

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

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
Click to expand...
=---
 
Define "third week". Is that the third Monday? The week that contains the
third Sunday? The third week that has a full seven days?
Click to expand...

Ken,

Thanks for your interest:

Third week is third Monday

Ta

dg
 
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>

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
Click to expand...
=---
 
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))
Click to expand...

Ken,

Brilliant, thanks

dg
 
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.
 
Back
Top