Adding days to a date field

[Iram]

Hello.
I am using Access 2000 and I need your help in resolving a problem.
I have three fields in a table:

Incoming/Outgoing (Combo Box field)
Initial Contact Date
ETA Date

I would like to make the value in the ETA Date field be as follows:
If field "Incoming/Outgoing" equals "Incoming" then "ETA Date" should be 60
business days more than "Initial Contact Date" if else "ETA Date" should be
10 business days more than Initial "Contact Date". I would like to make this
all happen in the form and saved to the "ETA Date" field. Would I put
whatever formula you provide me in the default field of "ETA Date" properties?

Thanks.
Iram/mcp

 

[Ofer Cohen]

Try

ETA Date: IIf([Incoming/Outgoing]=[Incoming], [Initial Contact Date] + 60,
[Contact Date] + 10)

It's not recomanded saving a calculated field in a table, you'll need to
maintain it, and it can cause errors.
You can always get the right resault using a query as displayed above.

 

[Iram]

Thanks for the info. Does your formula remove weekends and holidays?
I need the formula to remove weekends and holidays.

Thanks.
Iram

Try

ETA Date: IIf([Incoming/Outgoing]=[Incoming], [Initial Contact Date] + 60,
[Contact Date] + 10)

It's not recomanded saving a calculated field in a table, you'll need to
maintain it, and it can cause errors.
You can always get the right resault using a query as displayed above.

--
HTH, Good Luck
BS"D

Hello.
I am using Access 2000 and I need your help in resolving a problem.
I have three fields in a table:

Incoming/Outgoing (Combo Box field)
Initial Contact Date
ETA Date

I would like to make the value in the ETA Date field be as follows:
If field "Incoming/Outgoing" equals "Incoming" then "ETA Date" should be 60
business days more than "Initial Contact Date" if else "ETA Date" should be
10 business days more than Initial "Contact Date". I would like to make this
all happen in the form and saved to the "ETA Date" field. Would I put
whatever formula you provide me in the default field of "ETA Date" properties?

Thanks.
Iram/mcp

 

[Ofer Cohen]

Check this link

http://www.mvps.org/access/datetime/date0012.htm

--
HTH, Good Luck
BS"D

Thanks for the info. Does your formula remove weekends and holidays?
I need the formula to remove weekends and holidays.

Thanks.
Iram

Try

ETA Date: IIf([Incoming/Outgoing]=[Incoming], [Initial Contact Date] + 60,
[Contact Date] + 10)

It's not recomanded saving a calculated field in a table, you'll need to
maintain it, and it can cause errors.
You can always get the right resault using a query as displayed above.

--
HTH, Good Luck
BS"D

Hello.
I am using Access 2000 and I need your help in resolving a problem.
I have three fields in a table:

Incoming/Outgoing (Combo Box field)
Initial Contact Date
ETA Date

I would like to make the value in the ETA Date field be as follows:
If field "Incoming/Outgoing" equals "Incoming" then "ETA Date" should be 60
business days more than "Initial Contact Date" if else "ETA Date" should be
10 business days more than Initial "Contact Date". I would like to make this
all happen in the form and saved to the "ETA Date" field. Would I put
whatever formula you provide me in the default field of "ETA Date" properties?

Thanks.
Iram/mcp

 

[Ron2005]

1) create a holiday table that ONLY includes the week/work days that
will really be off (Monday if holiday is on Sunday)
2) the following will tell you how many week days are between two
dates:


wrkDays = DateDiff("d", StartDte, EndDte) - (DateDiff("ww",
StartDte, EndDte, 7) + DateDiff("ww", StartDte, EndDte, 1))


3) Count the number (with dCount or a query) of holidays between the
two dates
4) subtract 3 from 2 and you have it.


Check math.... you may have to add 1 depending on whether the first day

is counted. If it starts today and ends tomorrow is that 1 or 2
workdays? Your decision.


Ron

 

[Ron2005]

My last post actually was for the reverse of what you want to do.

I have found no easy way of doing what you want. When I had to do it. I
called a function that basically looped through for the number of work
days desired, but as it added 1 each time it checked for a weekends and
skipped those days and also checked if the date was in the holiday
table and skipped those.

Remember to allow someone a relatively easy way of updating that
holiday table and schedule it be done at least 2 months before year end.