Future Date

[David Snyder]

1. I am using Access 2007
2. I am a total novice so be kind please
3. I have a field ISRT_Opened_dat e
4. I need to calulate 45 work days out to a unbound text box called dpe_due_date

I have been trying to put this formula in the control source of the unbound tex box called dpe_due_date

[NeoPa]

What is a "work day" as far as you're concerned?

You talk about a TextBox (a Form Control), and you talk about a field (a Table element for holding data). These two concepts are not compatible. Are we really just talking about two controls on a form where one is bound but the other is not?

[David Snyder]

I have a field for the start date, I would like to add a field to the table for the due date. I will be running query and reports for due dates within 60 days.

[NeoPa]

That answers neither of my questions David :-(

It does raise another though. If the due date is determined algorithmically (as you've implied), why would you want to burden yourself with an extra field for it?

[David Snyder]

Thanks for you quick Reply NeoPa. A couple things to consider... First, I am just a novice trying to create a working database for our training department. I am sure that many things I am doing would make you scream NOOOOO. Let me explain what I am trying to do and you can point me in the right direction. We have duty positions that require certification, from the time they are opened up for that position (ISRT_opened_da te) and must be evaluated 45 working days later. I want to run reports based off of the number of days left prior to the evaluation date.

[NeoPa]

Fine. In that case you won't need to store the other date. Any report can be bound to a query and the query can work out the date 45 days after the opened date just as well as (if not more easily) than an update query. You needn't store information that is already identifiable.

That said, I still need answers to the two questions included in my post #2. To help you I need to know what you think you are saying (to understand what help is required).

For instance, Work Days could mean elapsed weekdays, or it could mean only those weekdays that are not public holidays. Handling public holidays within the calculation when the calculation is designed to run at any theoretical point in time (12/23/2246 for instance) is not something that can easily be fitted into a simple formula, whereas weekdays fit a pattern within modular arithmetic.

[David Snyder]

For this case, Work Days are five days a week but not always Mon - Fri. Holidays will not be an issue as we work all holidays, we are a 24 hour operation.

[NeoPa]

(I feel like a dentist.)

And the other question?

[David Snyder]

(I feel like a student being drilled by my teacher :o) )


What is the other question? I am missing something

Oh yeah ..... thanks for your help

[NeoPa]

From post #2 :

You talk about a TextBox (a Form Control), and you talk about a field (a Table element for holding data). These two concepts are not compatible. Are we really just talking about two controls on a form where one is bound but the other is not?

[David Snyder]

the start date is a field, the due date is what I am trying to add, I could add it as a field or if it makes more sense make it a form control. I am looking for direction.

[NeoPa]

That's not an answer I was expecting, but we do have answers at least so we can continue from that point.

I expect the first step is to ensure that there is a control on your form to handle the [ISRT_Opened_dat e] field. I will call that [txtISRT_Opened_ Date] for now.

Once that is available we will need to ensure that the unbound TextBox [DPE_Due_Date] always reflects a date that is 45 work-days from the date shown in there. This is most easily done by putting a formula in [DPE_Due_Date].

Using the basic formula of 5 working days to every 7 days or week (45 / 5 = 9 ==> 9 * 7 = 63) we would use :

=DateAdd("d", 63, [txtISRT_Opened_Date])

There are more complicated approaches to this, but from what you say they are not required for what you need.

[Rabbit]

First off, I'm a little confused about the 24 hour a day operation but only working 5 "random" days out of a week". But because of the 5 "random" days out of the week part, the formula could fall on a day when people aren't working.

[NeoPa]

As 45 is a multiple of 5 though, we know we are dealing exclusively with whole weeks. I can't imagine a situation where data is likely to fall foul of this possibility in these circumstances.

[Rabbit]

It could in the following scenario.

The Opened Date is 01/03/2011. On a Monday. This week, they work Monday through Friday.

If you add 63 days, that's 03/07/2011. On a Monday. Which would technically be the 45th or 46th working day.

Assuming Sunday is the beginning of a week. It would be the 46th working day if they are working Sunday through Thursday that week.

Assuming Monday is considered the start of a week, you are assuming they are working that Monday. Which may not be the case. That week, they may decide to work Tuesday through Saturday. Which would make that Tuesday the 45th working day.