How in Access do I use Date()+2 missing weeknds (e.g Thu+2 = Mon)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary Donovan - 09 Nov 2005 14:37 GMT

I'm using a query that has a field date()+2 to give me a flag date 2 days
ahead of today. This is fine for a Monday, Tuesday or Wednesday where the
flag date is therefore Wednesday, Thursday or Friday. However, I need the
flag date when run on a Thursday to return the date of the following Monday,
and for a Friday the following Tuesday - i.e. to work on a working day basis
leavng out weekends.
Is this possible?
Thanks all
Gary Donovan

Reply to this Message

Chris M - 09 Nov 2005 15:04 GMT

Well, there's a function WeekDay() which returns 1-7 representing the day of
the week.
I guess you could construct a fairly simple function that varied the number
of days to add depending of the day of the week of the original date.

eg.

date() + (iif(WeekDay(Date()),4,5,iif(WeekDay(Date()),6.......... etc.

Chris.

> I'm using a query that has a field date()+2 to give me a flag date 2 days
> ahead of today. This is fine for a Monday, Tuesday or Wednesday where the
[quoted text clipped - 7 lines]
> Thanks all
> Gary Donovan

Reply to this Message

Ofer - 09 Nov 2005 15:51 GMT

Hi Gary
There is a post given by Allen Browne

See:
Doing WorkDay Math in VBA
at:
http://www.mvps.org/access/datetime/date0012.htm

Signature

The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

> I'm using a query that has a field date()+2 to give me a flag date 2 days
> ahead of today. This is fine for a Monday, Tuesday or Wednesday where the
[quoted text clipped - 5 lines]
> Thanks all
> Gary Donovan

Reply to this Message