Find date

[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

 

[Ken Snell]

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

=---

 

[VillageIdiot]

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

 

[Ken Snell]

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

=---

 

[VillageIdiot]

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

 

[Hez]

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.