Date field question

[Bob Waggoner]

I have PMs that are due - sometimes within 1 day and sometimes within 3 days.
I record the date done and have a query extract the next due date. When that
falls on a weekend, how do I get the date to advance to the following Monday
for a due date? Here's my code now.
Due Date: (([MaxOfDateDone]+[FreqDays]))
Thanks

 

[KARL DEWEY]

[Try this --
IIf(Weekday([MaxOfDateDone]+[FreqDays]) Between 2 And
6,[MaxOfDateDone]+[FreqDays],IIf(Weekday([MaxOfDateDone]+[FreqDays]+1)
Between 2 And 6,[MaxOfDateDone]+[FreqDays]+1,[MaxOfDateDone]+[FreqDays]+2))

 

[fredg]

I have PMs that are due - sometimes within 1 day and sometimes within 3 days.
I record the date done and have a query extract the next due date. When that
falls on a weekend, how do I get the date to advance to the following Monday
for a due date? Here's my code now.
Due Date: (([MaxOfDateDone]+[FreqDays]))
Thanks

Add 2 days if the resulting date is a Saturday, add 1 day of the
resulting date is a Sunday.
Perhaps this will help:

DueDate: IIf(Weekday([ADate]+[FreqDays])=7,([ADate]+[FreqDays])+2,
IIf(Weekday([ADate]+[FreqDays])=1,([ADate]+[FreqDays])+1,[ADate]+[FreqDays]))

 

[Bob Waggoner]

Absolutely works! Thank you! Awesome.

[Try this --
IIf(Weekday([MaxOfDateDone]+[FreqDays]) Between 2 And
6,[MaxOfDateDone]+[FreqDays],IIf(Weekday([MaxOfDateDone]+[FreqDays]+1)
Between 2 And 6,[MaxOfDateDone]+[FreqDays]+1,[MaxOfDateDone]+[FreqDays]+2))

I have PMs that are due - sometimes within 1 day and sometimes within 3 days.
I record the date done and have a query extract the next due date. When that
falls on a weekend, how do I get the date to advance to the following Monday
for a due date? Here's my code now.
Due Date: (([MaxOfDateDone]+[FreqDays]))
Thanks

 

[Bob Waggoner]

Thank you!

I have PMs that are due - sometimes within 1 day and sometimes within 3 days.
I record the date done and have a query extract the next due date. When that
falls on a weekend, how do I get the date to advance to the following Monday
for a due date? Here's my code now.
Due Date: (([MaxOfDateDone]+[FreqDays]))
Thanks

Add 2 days if the resulting date is a Saturday, add 1 day of the
resulting date is a Sunday.
Perhaps this will help:

DueDate: IIf(Weekday([ADate]+[FreqDays])=7,([ADate]+[FreqDays])+2,
IIf(Weekday([ADate]+[FreqDays])=1,([ADate]+[FreqDays])+1,[ADate]+[FreqDays]))

 

[Jeff Boyce]

Here in the USA, this coming Friday is an observed holiday. How would you
want the function/routine to handle that?

Some holidays are observed on a Monday. How would you want the
function/routine to handle that?

Consider taking a look at mvps.org/access to see about a WorkDays()
function -- it may offer ideas toward what you're working on.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Waggoner]

Thanks, Jeff. I've taken your advice and added them to my favorites list. I
Appreciate the help.
Bob

Here in the USA, this coming Friday is an observed holiday. How would you
want the function/routine to handle that?

Some holidays are observed on a Monday. How would you want the
function/routine to handle that?

Consider taking a look at mvps.org/access to see about a WorkDays()
function -- it may offer ideas toward what you're working on.


Regards

Jeff Boyce
Microsoft Office/Access MVP

I have PMs that are due - sometimes within 1 day and sometimes within 3
days.
I record the date done and have a query extract the next due date. When
that
falls on a weekend, how do I get the date to advance to the following
Monday
for a due date? Here's my code now.
Due Date: (([MaxOfDateDone]+[FreqDays]))
Thanks