Date Calculations

[InfoNow]

Again, I am trying to get a date without the holidays and weekends.
Fields in the table, [DateFreq], [NextDueDate], and [LastMaintDate]. What I am trying to do is, when a user input the [LastMaintDate], the DB calculates the [NextDueDate] based on the [DateFreq] & [LastMaintDate]. The [NextDueDate] will fall only on a weekday that is not a holiday.
For Example: [DateFreq]= 7
[LastMaintDate]= 06/19/02
so, [NextDueDate] should = 06/28/02, skipping the weekends.

I know there is faq181-261 but I don't know how to put it to work for me. Do I just copy and paste it into a modules and name it DELTADAYS??? I've created the tblHolidays table as it says, but have no idea what to do. What exactly do I have to do with all those codes?

Please help...
TIA

 

[grnzbra]

What do you want to happen if the next due date is a holiday or weekend?

 

[SgtJarrow]

InfoNow,
Please take a look at thread181-290809. I think you will find everything you need there. I gave the code and some instructions on how to do everything you want to do. This code will even allow you to include any holidays you want....i.e. non-standard holidays.
Please let me know if you need any help with this code. It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[InfoNow]

grnbra,
If the NextDueDate should fall on a holiday or weekend, then just move the NextDueDate to the next business day.

mstrmage1768,
I don't know what to do with the code you created on that thread. Can you please show me exactly what I need to do? When you said:
_______________________________________________________
Just put the below into a module and call it as such:

funAddWorkDays(2, dtmAssignedDate, Array(#12/25/01#, #1/1/02#))
_______________________________________________________
What does this mean? Put the code in the Modules tab or in the form? What do you mean "call it as such?"
Sorry for all the newby questions, but I am not a programmer, as you can already tell.

TIA

 

[SgtJarrow]

Info....not a problem, that is what I meant about asking for help....

Click on the module tab...click new....This will open a new module. Simply copy and paste all the code that I referenced into this module. Save the module. Just leave whatever default name is supplied...really doesn't matter.

Then, whereever you want to make the date calculation, use the code call I supplied...

For example...based on your example you have three fields:
[datefreq] is the number of "working" days to add
[lastmaintdate] is the date to calculate from
[nextduedate] is the field that displays the new date

create a command button called calculateduedate. In the OnClick property for this button, place the following:

Me![nextduedate] = funAddWorkDays([datefreq], [lastmaintdate], Array(#12/25/01#, #1/1/02#))

And simply change the Array portion to the holidays you also want to exclude, one at a time, surrounded by #s and seperated commas. Weekends are already excluded by the program...no need for you to worry about those.

If you are still having difficulties with it, please shoot me an email to the address in my profile and I will shoot you back a small database with this setup for you so you can see it in action. Hope this works out for you. It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[InfoNow]

mstrmage1768,

it works beautifully!! Thank you so much for your help.

InfoNow.