calculate date - no weekends

[Jenn]

I am trying to write a query to calculate the date of a report three
days after the admit date. The report cannot be due on sat or sun so
if the third day falls on a sat it will be due the fri before and if it
falls on a sun it is due the following mon. I have this code started
but it is saying there is the wong number of arguments. Any ideas?
Thanks Jenn

CRISIS REPOT: IIf([PATIENT ADMIT DATE] <> [ODD] Or [ODD] Is Null,
IIf(Weekday(DateAdd("d", 3, [PATIENT ADMIT DATE]), 2) = 6, DateAdd("d",
2,
[PATIENT ADMIT DATE]), (IIf(Weekday(DateAdd("d", 3, [PATIENT ADMIT
DATE]), 2)
= 7, DateAdd("d", 4, [PATIENT ADMIT DATE]), DateAdd("d",3,[PATIENT
ADMIT
DATE]))), ""), "")

 

[John Spencer]

This might be better handled by writing a custom function, but it can be
done with VBA functions in a query.

To determine number of days to add depending on the weekday, you could use
the Choose function
Choose(Weekday([PATIENT ADMIT DATE] ),3,3,3,2,4,3,3)

How about rewriting it as
IIf([PATIENT ADMIT DATE] <> [ODD] Or [ODD] Is Null
, DateAdd("d", Choose(Weekday([PATIENT ADMIT DATE] ),3,3,3,2,4,3,3),[Patient
Admit Date])
, Null)

The advantage of writing a custom VBA function to do this is that if someone
decides that reports due on Saturday should be moved to Monday then you only
have to change the function. If you do a formula in a query then you have
to find and rewrite the formula every place you are using it.

 

[Jenn]

John

Thanks this was very helpful!

Jenn

This might be better handled by writing a custom function, but it can be
done with VBA functions in a query.

To determine number of days to add depending on the weekday, you could use
the Choose function
Choose(Weekday([PATIENT ADMIT DATE] ),3,3,3,2,4,3,3)

How about rewriting it as
IIf([PATIENT ADMIT DATE] <> [ODD] Or [ODD] Is Null
, DateAdd("d", Choose(Weekday([PATIENT ADMIT DATE] ),3,3,3,2,4,3,3),[Patient
Admit Date])
, Null)

The advantage of writing a custom VBA function to do this is that if someone
decides that reports due on Saturday should be moved to Monday then you only
have to change the function. If you do a formula in a query then you have
to find and rewrite the formula every place you are using it.

I am trying to write a query to calculate the date of a report three
days after the admit date. The report cannot be due on sat or sun so
if the third day falls on a sat it will be due the fri before and if it
falls on a sun it is due the following mon. I have this code started
but it is saying there is the wong number of arguments. Any ideas?
Thanks Jenn

CRISIS REPOT: IIf([PATIENT ADMIT DATE] <> [ODD] Or [ODD] Is Null,
IIf(Weekday(DateAdd("d", 3, [PATIENT ADMIT DATE]), 2) = 6, DateAdd("d",
2,
[PATIENT ADMIT DATE]), (IIf(Weekday(DateAdd("d", 3, [PATIENT ADMIT
DATE]), 2)
= 7, DateAdd("d", 4, [PATIENT ADMIT DATE]), DateAdd("d",3,[PATIENT
ADMIT
DATE]))), ""), "")

 

[Jenn]

I had another question-- is there a way to manually put in the date of
an expression? For example if I wanted another expression to calculate
from the below expression, this calculated today's date 11/23/06 and I
wanted to change it to 11/24/06 (only will happen once in a great
while) so that my next calculation will be counted 7 days after the
24th.

I hope this makes sense..
Jenn

John

Thanks this was very helpful!

Jenn

This might be better handled by writing a custom function, but it can be
done with VBA functions in a query.

To determine number of days to add depending on the weekday, you could use
the Choose function
Choose(Weekday([PATIENT ADMIT DATE] ),3,3,3,2,4,3,3)

How about rewriting it as
IIf([PATIENT ADMIT DATE] <> [ODD] Or [ODD] Is Null
, DateAdd("d", Choose(Weekday([PATIENT ADMIT DATE] ),3,3,3,2,4,3,3),[Patient
Admit Date])
, Null)

The advantage of writing a custom VBA function to do this is that if someone
decides that reports due on Saturday should be moved to Monday then you only
have to change the function. If you do a formula in a query then you have
to find and rewrite the formula every place you are using it.

I am trying to write a query to calculate the date of a report three
days after the admit date. The report cannot be due on sat or sun so
if the third day falls on a sat it will be due the fri before and if it
falls on a sun it is due the following mon. I have this code started
but it is saying there is the wong number of arguments. Any ideas?
Thanks Jenn

CRISIS REPOT: IIf([PATIENT ADMIT DATE] <> [ODD] Or [ODD] Is Null,
IIf(Weekday(DateAdd("d", 3, [PATIENT ADMIT DATE]), 2) = 6, DateAdd("d",
2,
[PATIENT ADMIT DATE]), (IIf(Weekday(DateAdd("d", 3, [PATIENT ADMIT
DATE]), 2)
= 7, DateAdd("d", 4, [PATIENT ADMIT DATE]), DateAdd("d",3,[PATIENT
ADMIT
DATE]))), ""), "")