Calculating date

J

Jenn

I had some great help in adding three days to an admission date and if
this date landed on a sat/sun to report the next mon, while I guess
that it was suppose to be the closest business day so for sat it should
be the fri before and if it is sun it should be the mon. I have tried
to adjust the following code but keep getting errors. Any help would be
appreciated.

This code works for reporting the following mon:

CRISIS POT: IIf([PATIENT ADMIT DATE]<>[ODD] Or [ODD] Is
Null,(IIf(Weekday(DateAdd("d",3,[PATIENT ADMIT
DATE]),2)>=6,DateAdd("d",10,[PATIENT ADMIT
DATE])-Weekday(DateAdd("d",3,[PATIENT ADMIT
DATE]),2)+1,DateAdd("d",3,[PATIENT ADMIT DATE]))),"")

This is how I was trying to adjust it:

CRISIS POT: IIf
([PATIENT ADMIT DATE]<>[ODD] Or [ODD] Is Null, ((IIf
(Weekday(DateAdd("d",3,[PATIENT ADMIT DATE])
,2)=7, DateAdd("d",10,[PATIENT ADMIT
DATE])-Weekday(DateAdd("d",3,[PATIENT ADMIT DATE]),2)+1,
DateAdd("d",3,[PATIENT ADMIT DATE])) Or
(IIf(Weekday(DateAdd("d",3,[PATIENT ADMIT DATE]),2)=6,
DateAdd("d",10,[PATIENT ADMIT DATE])-Weekday(DateAdd("d",3,[PATIENT
ADMIT DATE]),2)-1, DateAdd("d",3,[PATIENT ADMIT DATE]))),"")


Thanks
Jenn
 
G

Guest

Hi Jenn,

how about;
IIf( said:
Null,CRISIS POT: 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]), "")), ""),"")
Click to expand...

If date 3 days hence is a saturday, add 2 days (friday) else if sunday, add
4 days(monday), or change to 9 or 11 days if you want the following week.

hope that helps,

TonyT..


Jenn said:
I had some great help in adding three days to an admission date and if
this date landed on a sat/sun to report the next mon, while I guess
that it was suppose to be the closest business day so for sat it should
be the fri before and if it is sun it should be the mon. I have tried
to adjust the following code but keep getting errors. Any help would be
appreciated.

This code works for reporting the following mon:

CRISIS POT: IIf([PATIENT ADMIT DATE]<>[ODD] Or [ODD] Is
Null,(IIf(Weekday(DateAdd("d",3,[PATIENT ADMIT
DATE]),2)>=6,DateAdd("d",10,[PATIENT ADMIT
DATE])-Weekday(DateAdd("d",3,[PATIENT ADMIT
DATE]),2)+1,DateAdd("d",3,[PATIENT ADMIT DATE]))),"")

This is how I was trying to adjust it:

CRISIS POT: IIf
([PATIENT ADMIT DATE]<>[ODD] Or [ODD] Is Null, ((IIf
(Weekday(DateAdd("d",3,[PATIENT ADMIT DATE])
,2)=7, DateAdd("d",10,[PATIENT ADMIT
DATE])-Weekday(DateAdd("d",3,[PATIENT ADMIT DATE]),2)+1,
DateAdd("d",3,[PATIENT ADMIT DATE])) Or
(IIf(Weekday(DateAdd("d",3,[PATIENT ADMIT DATE]),2)=6,
DateAdd("d",10,[PATIENT ADMIT DATE])-Weekday(DateAdd("d",3,[PATIENT
ADMIT DATE]),2)-1, DateAdd("d",3,[PATIENT ADMIT DATE]))),"")


Thanks
Jenn
Click to expand...
 
J

Jenn

Hi Tony

Thanks a few questions was there a reason the expression name was after
the null expression? Also It was giving error concerning expressions
number so I adjusted it a little and now it says there is a syntex
error (comma) in expressions, here it is:
CRISIS POT: 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])),"")),""),"")

What do you think??
Jenn
Hi Jenn,

how about;
IIf( said:
Null,CRISIS POT: 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]), "")), ""),"")
Click to expand...

If date 3 days hence is a saturday, add 2 days (friday) else if sunday, add
4 days(monday), or change to 9 or 11 days if you want the following week.

hope that helps,

TonyT..


Jenn said:
I had some great help in adding three days to an admission date and if
this date landed on a sat/sun to report the next mon, while I guess
that it was suppose to be the closest business day so for sat it should
be the fri before and if it is sun it should be the mon. I have tried
to adjust the following code but keep getting errors. Any help would be
appreciated.

This code works for reporting the following mon:

CRISIS POT: IIf([PATIENT ADMIT DATE]<>[ODD] Or [ODD] Is
Null,(IIf(Weekday(DateAdd("d",3,[PATIENT ADMIT
DATE]),2)>=6,DateAdd("d",10,[PATIENT ADMIT
DATE])-Weekday(DateAdd("d",3,[PATIENT ADMIT
DATE]),2)+1,DateAdd("d",3,[PATIENT ADMIT DATE]))),"")

This is how I was trying to adjust it:

CRISIS POT: IIf
([PATIENT ADMIT DATE]<>[ODD] Or [ODD] Is Null, ((IIf
(Weekday(DateAdd("d",3,[PATIENT ADMIT DATE])
,2)=7, DateAdd("d",10,[PATIENT ADMIT
DATE])-Weekday(DateAdd("d",3,[PATIENT ADMIT DATE]),2)+1,
DateAdd("d",3,[PATIENT ADMIT DATE])) Or
(IIf(Weekday(DateAdd("d",3,[PATIENT ADMIT DATE]),2)=6,
DateAdd("d",10,[PATIENT ADMIT DATE])-Weekday(DateAdd("d",3,[PATIENT
ADMIT DATE]),2)-1, DateAdd("d",3,[PATIENT ADMIT DATE]))),"")


Thanks
Jenn
Click to expand...
Click to expand...
 
G

Guest

Hi again Jenn,

replies in order,

Jenn said:
Hi Tony

Thanks a few questions was there a reason the expression name was after
the null expression?
Click to expand...
No sorry, don't know how that happened, mix up copy and pasting from my
renamed testing i guess :p

Also It was giving error concerning expressions
number so I adjusted it a little and now it says there is a syntex
error (comma) in expressions, here it is:
CRISIS POT: 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])),"")),""),"")
Click to expand...
Your problem above is the double(( after the first Iif, it is enclosing
everything but ,""),"") and is cancelling out the whole equation.
Also see you noticed I missed out the add 3 days if neither is true - added
below.

Just spent 5 minutes trying to pick the bones out of your one above, and
it's been too long a day for the time i've got available, sorry, convinced
mine is ok now :p
What do you think??
Click to expand...

This works for me;
CRISIS POT: 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]))), ""), "")

As per my original post, but putting CRISIS POT: in the right place gives me
no syntax errors at all so should be fine.

Good luck & post back if that still doesn't work!!!

TonyT..
 
J

Jenn

Tony,
Thanks for helping - but it is now saying I have the wrong number of
arguements. I keep thinking we are just missing a parenthesis
somewhere. What do you think?

Thanks!
Jenn

Hi again Jenn,

replies in order,

Jenn said:
Hi Tony

Thanks a few questions was there a reason the expression name was after
the null expression?
Click to expand...
No sorry, don't know how that happened, mix up copy and pasting from my
renamed testing i guess :p

Also It was giving error concerning expressions
number so I adjusted it a little and now it says there is a syntex
error (comma) in expressions, here it is:
CRISIS POT: 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])),"")),""),"")
Click to expand...
Your problem above is the double(( after the first Iif, it is enclosing
everything but ,""),"") and is cancelling out the whole equation.
Also see you noticed I missed out the add 3 days if neither is true - added
below.

Just spent 5 minutes trying to pick the bones out of your one above, and
it's been too long a day for the time i've got available, sorry, convinced
mine is ok now :p
What do you think??
Click to expand...

This works for me;
CRISIS POT: 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]))), ""), "")

As per my original post, but putting CRISIS POT: in the right place gives me
no syntax errors at all so should be fine.

Good luck & post back if that still doesn't work!!!

TonyT..
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculate date - no weekends 3
calculating date 10
Date Time Picker (MSComCtl2.DTPicker.2) 1
Access Building a IIF expression in Access 0
Help with Invalid Use of NULL and Dsum function 3
Calculate Future Date 4
DATEDD FUNCTION 7
Date Calculator 5

Top