calculating date

[Jenn]

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")

Thanks

 

[Allen Browne]

Use Weekday() to get the day of the week.
Sat/Sun will be 6 and 7 if you start the week with Monday, i.e.:
Weekday([the date], 2) >=6

You will end up with an expression along these lines:
IIf(Weekday(DateAdd("d", 3, [TheDate]),2) >= 6,
DateAdd("d", 10, [TheDate]) - Weekday(DateAdd("d", 3, [TheDate]),2) + 1
,
DateAdd("d", 3, [TheDate]))

 

[Marshall Barton]

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")

Crisis: IIf([ADMIT DATE]<ODDate, DateAdd("d", 3 ,[ADMIT
DATE]) + IIf(DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7)
< 3, 3 - DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7),
0), Null)

You might want to consider creating a function to
encapsulate such a complex expression.

 

[Klatuu]

Crisis: IIf([ADMIT DATE]<>[ODDate],[ADMIT DATE] + IIf(WeekDay([ADMIT DATE] +
3, 2) <= 5, 3, 11 - WeekDay([ADMIT DATE],2)),"")

 

[Jenn]

How do I get the default to start on Monday - because my default is
Sunday?
Thanks

Use Weekday() to get the day of the week.
Sat/Sun will be 6 and 7 if you start the week with Monday, i.e.:
Weekday([the date], 2) >=6

You will end up with an expression along these lines:
IIf(Weekday(DateAdd("d", 3, [TheDate]),2) >= 6,
DateAdd("d", 10, [TheDate]) - Weekday(DateAdd("d", 3, [TheDate]),2) + 1
,
DateAdd("d", 3, [TheDate]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")

Thanks

 

[Jenn]

Thanks for responding I tried this and it is not picking up my admit
date it is asking for me to enter it- Is there a reason for that?
Jenn

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")

Crisis: IIf([ADMIT DATE]<ODDate, DateAdd("d", 3 ,[ADMIT
DATE]) + IIf(DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7)
< 3, 3 - DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7),
0), Null)

You might want to consider creating a function to
encapsulate such a complex expression.

 

[Klatuu]

It means it does not know what [ADMIT DATE] is. Your post made us believe it
is a field in your query. What is it, really?

Thanks for responding I tried this and it is not picking up my admit
date it is asking for me to enter it- Is there a reason for that?
Jenn

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")

Crisis: IIf([ADMIT DATE]<ODDate, DateAdd("d", 3 ,[ADMIT
DATE]) + IIf(DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7)
< 3, 3 - DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7),
0), Null)

You might want to consider creating a function to
encapsulate such a complex expression.

 

[Jenn]

It is a field - that's why I was confused on why it would ask for
input..

It means it does not know what [ADMIT DATE] is. Your post made us believe it
is a field in your query. What is it, really?

Thanks for responding I tried this and it is not picking up my admit
date it is asking for me to enter it- Is there a reason for that?
Jenn

Jenn wrote:

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")


Crisis: IIf([ADMIT DATE]<ODDate, DateAdd("d", 3 ,[ADMIT
DATE]) + IIf(DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7)
< 3, 3 - DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7),
0), Null)

You might want to consider creating a function to
encapsulate such a complex expression.

 

[Klatuu]

Check all the spellings to be sure they match the correct spelling of the
field name.

It is a field - that's why I was confused on why it would ask for
input..

It means it does not know what [ADMIT DATE] is. Your post made us believe it
is a field in your query. What is it, really?

Thanks for responding I tried this and it is not picking up my admit
date it is asking for me to enter it- Is there a reason for that?
Jenn

Marshall Barton wrote:
Jenn wrote:

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")


Crisis: IIf([ADMIT DATE]<ODDate, DateAdd("d", 3 ,[ADMIT
DATE]) + IIf(DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7)
< 3, 3 - DatePart("w", DateAdd("d", 3 ,[ADMIT DATE]), 7),
0), Null)

You might want to consider creating a function to
encapsulate such a complex expression.

 

[Allen Browne]

In the example, there are 2 values inside Weekday().
The second is a 2.
That makes Weekday() calculate the days using Monday as the first day of the
week.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I get the default to start on Monday - because my default is
Sunday?
Thanks

Use Weekday() to get the day of the week.
Sat/Sun will be 6 and 7 if you start the week with Monday, i.e.:
Weekday([the date], 2) >=6

You will end up with an expression along these lines:
IIf(Weekday(DateAdd("d", 3, [TheDate]),2) >= 6,
DateAdd("d", 10, [TheDate]) - Weekday(DateAdd("d", 3, [TheDate]),2) +
1
,
DateAdd("d", 3, [TheDate]))

I am trying to perform a date calculation in Access 2003. I need the
calculation to add three days to the entered date and if it is
saturday/sunday I want it to print out the following monday. This is
what I am starting with but I don't know hot to get it to exclude
sat/sun)

Crisis: IIf([ADMIT DATE]<>[ODDate],DateAdd("w",3,[ADMIT DATE]),"")

 

[Jenn]

Thank you everyone- It worked!!
Jenn