Calculated Date Fields

[BellKev]

Hello,

I'm putting a database together for some people at my office. I want to populate some fields with calculated dates based on a date that is input. How would I do this? Is there anyway to only let it count business days (I.e not include weekends or holidays)? Any help is great.

 

[BobJacksonNCI]

Simple!

First, you click the button at the top that says "Advanced Search".

Then, you type in business days in the search criteria.

Select exact match.

Peruse the many threads that discuss the topic.

Sorry if that sounded sarcastic!

Recommend you look at:

http://www.tek-tips.com/faqs.cfm?spid=181&sfid=261&newpid=181

HTH,
Bob

 

[BellKev]

Thanks for the reply. Not too sure what the searching is supposed to be doing. I'm trying tohave fields auto populate with target dates based on a completion date. The related link is a bit of a help though as I'm working through it, thanks.

 

[EBGreen]

Try this thread:
thread181-47925

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[BellKev]

Is there anyway I can use the DateAdd function? I'm trying to use this as the default value for the calculated field. However I'm having difficulty in using the [Date1] field in the same table. Any way that it is possible to use the DateAdd function this way?

 

[BobJacksonNCI]

BellKev,

To "... only let it count business days ..." you have to have logic to avoid including non-business days.

Someone here can assist with counting Monday - Friday and not Saturday - Sunday. However, holidays can vary, so that requires adding a table to the mix that includes each holiday your company observes.

For the best help, post a few examples of the starting date, number of days and desired calculated date.

As I said, the logic be fixed if you only need to avoid Saturdays and Sundays. But if you need to leave out holidays, such as Memorial Day, it takes more maintenance - gotta identify the holidays and maintain the table going forward.





HTH,
Bob

 

[EBGreen]

The DateAdd function won't account for weekends and holidays.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[lespaul]

It sounds like you are trying to take a significant date (let's say you checked a book out of the library = checkout date) and you now want to calculate the due date and store it in your table.

Is that kinda what you are trying to do? Then don't! You are trying to store a calculated field in the table and that breaks 3NF (if you're not sure what 3NF is check out 'Fundamentals of Relational Database Design'). Now there are instances where breaking 3NF is called for, but they are few and far between! You should calculate dates like that on the fly in the query rather than storing them.

Leslie

 

[BobJacksonNCI]

BellKev,

First, I apologize for my short answers to your real questions!

Les is a skilled contributor, and his advice is always sound! But, you still need the logic to calculate your target date.

Logically, the only way to take a starting date and add a number of business days to that to come up with a calculated end date is to create a loop. Which is probably best handled with a function.

The hotlinks posted calculate the number of business days between two dates - not exactly what you're looking for, but similar logic is required.

Think of the logic you need:

Start
Load beginning date into a variable (vDate)
Define Counter variable (vCounter)
Loop
Is vDate Sat or Sun?
Yes (need to skip counting this date)
Advance vDate (vDate + 1)
Go to loop
No
Add 1 to vCounter
Is vCounter variable = to number of days specified?
Yes
I'm done - vDate contains the target date
No
Advance vDate (vDate +1)
Go to loop
End

That only allows for Saturdays and Sundays, not holidays.

The actual Visual Basic instructions may benefit from built-in functions (e.g., Workday), but the loop is required.

You CAN accomplish what you want...

It WILL probably take more work than you estimated.



HTH,
Bob

 

[lespaul]

no need to apologize, but it's her - no offense taken!! Thanks for the compliment!!

leslie

 

[BellKev]

Hmmm ... ok ... I've been trying to get this straight but I don't have formal VB or any Access training in depth. I've read these posts and am trying to get my head around them ...

What I'm trying to do ...
I have a final Completion Date (CompDate), I want to "Schedule" various target dates backwards. So the interval to my very first target is -60 business days. Now how do I go about getting this date? I understand I need a loop to check the dates, I'm just having some trouble actually implementing.

 

[BobJacksonNCI]

lespaul,
You are welcome (compliment).

BellKev,

O.K., you will need to figure out the mechanics of creating a function in your database. I'm VERY computer-experienced, but learning the Access + VBA form of self-abuse myself.

So, you're better served performing an Advanced Search of these fora using function as your criteria to get good instruction for that part.

Then, where you need the target date (query or form control), type this:

yourtargetdatename = TargetDate(CompDate, 60)

Above uses CompDate as you stated above, as well as the number of business days to count back.

Following is the function you need to add to your database.
This is UNTESTED and contains no logic to identify erroneous CompDate being submitted.

Public Function TargetDate(CompDate As Date, BusDays As Int) As Date
Dim DayCount as Integer
TargetDate = CompDate

Do
If WeekDay(TargetDate) Not In (vbSaturday, vbSunday) then
DayCount = DayCount + 1
End If

TargetDate = DateWork - 1

Loop Until DayCount = BusDays

HTH,
Bob

 

[MichaelRed]

Whilst the exercise is good for those who want to learn (" ... we learn by doing ... "), there is also ye olde school aforementioned at the top ... search, to shortcut even that trivial exercise, go directly to faq-181-261





MichaelRed
mlred@verizon.net