calculating a date while taking into account holidays

[bryanm694]

I have been looking for quite some time for some vba code that would return a date based on values in 2 fields.

In other words, I already have a field that returns transit days based on the customer chosen. End users will then enter a DueDate for the order. I want a field that returns a "ScheduledShipDate" based on [DueDate]-[TransitDays]. The part that makes this more difficult, in my opinion, is the fact that I also need to take into account some specific holidays.

I have already constructed a table with a list of the 6 company holidays and their corresponding dates. [tblHolidays.HolidayDate]

Unfortunately, my vba knowhow might as well be limited to copy and pasting as I do not need to use it all that often. Any help would be greatly appreciated.

Thanks

 

[jdraw]

From your English description of the issue:

ScheduledShipDate = DueDate - TransitDays

and if there is a holiday(s) in the DueDate-TransitDays you would have to add the number of Holidays to the ScheduleShipDate (I think).
If there is 1 holiday in the period, how exactly does it affect your process?Do you have drivers doing the actual shipment? Do you use the post office or equivalent? Need more info for clarity.

So the question comes down to how many holidays in the DueDate-TransitDays.

Please show tables and any related query attempts/vba .

Here is a link to illustrate time frames and overlaps.

 

[bryanm694]

Yes, everything you posted is correct. I have not attempted any VBA yet other than a module that I found online. It is along the same lines of what I am looking for; however, this module returns a number of days rather than a date.

I believe that everything you asked for is in this file that I just threw together.

Note: Ignore the [ActualShipDate] field. it has absolutely nothing to do with this situation.

Thank you.

 

[jdraw]

I am getting unrecognized format on your file. I have Access 2010??

Closed some other stuff and it is opening???

 

[jdraw]

Here is a query to get the Number of Holidays between 2 dates Or
1 date and a specific transit days.

PARAMETERS PeriodStart DateTime, PeriodEnd DateTime;
SELECT  count(*) as NumHolidays
 FROM tblHolidays
WHERE  
tblHolidays.HolidayDate  Between PeriodStart and PeriodEnd;

You could turn this into a function quite easily.

 

[bryanm694]

The problem with that solution is that I am trying to get the "end date" to be automated. I only want the user to have to enter the DueDate on the PO. VBA would take care of everything else.

In other words, it appears that your code needs 2 dates as an input, whereas I would like to only be entering 1 date as an input.

I do appreciate that bit of code, though. It will be useful to me for other parts of this database in the future

 

[jdraw]

You can adapt the code to do what you need.

Start by writing in plain English,
the date you start with, the transit days and
cosider the function will take in a date, and transitDays and will calculate how many holidays are in that time period.

Do the english, in detail,
and I'll help you with the vba.

 

[bryanm694]

Are you looking for something like this?

Input:
DueDate:
This value is entered by the end user in the [DueDate] control

Transit Days:
This value is specific to each customer and is automatically filled in via an event procedure when the user enters a value in the [DueDate] control

Holidays:
These dates are defined on tblHolidays in the field [tblHolidays.HolidayDate]

Output:
The number of transit days PLUS any holidays/weekends SUBTRACTED from [DueDate]

Example:
WorkDays(12/28/2015, 3, [tblHolidays.HolidayDate]) would return 12/22/2015 Because the Due Date is 12/28/2015, but 3 days are required for transit and 12/25 is omitted as a holiday while 12/26 and 12/27 are omitted as weekend

 

[jdraw]

Does it work or not?
It isn't what I want, it's trying to get you to describe in English what you are trying to do, and the steps to do it.

How do holidays affect Transit Days?

From post#2

If there is 1 holiday in the period, how exactly does it affect your process?Do you have drivers doing the actual shipment? Do you use the post office or equivalent? Need more info for clarity.

 

[bryanm694]

All purchase orders include a due date. Our order entry person inputs this date. We want to have the database tell us the date that the order should ship based on two criteria: 1) number of days that it takes in transit to the customer and 2) the addition of holidays to the "transit" days if holidays fall between the ship date and the due date.

I could use the bit of code/query that you posted above to add to my field containing transit days. I could enter a due date of 3/20 and a transit day value of 3 days; your code could then count for any holidays between 3/20 and 3/17. If any were found, it would ADD to the transit time. Our company holidays always ADD to the transit time.

If I could write VBA, I would write a module that adds the number of holidays (if any) and weekends (if any) to the number of transit days that I have already defined. After that arithmetic, the VBA module would then SUBTRACT ([TransitDays]+[Holidays]+[Weekend]) from the Due Date.

I am sorry but I do not know how to be more clear with what I am asking.

 

[jdraw]

Ok, I think we have covered the basics. You have to account for holidays even if they fall within the transit time.
So, in general, if you put something to be delivered on the "truck", then any holidays have to be included.

I showed a query with 2 parameters. You can adapt that to a function or whatever.

A module is like a container or folder. You create procedures (sub or function) inside a module. You execute procedures.

Good luck with your project.

 

[Solo712]

Are you looking for something like this?

Input:
DueDate:
This value is entered by the end user in the [DueDate] control

Transit Days:
This value is specific to each customer and is automatically filled in via an event procedure when the user enters a value in the [DueDate] control

Holidays:
These dates are defined on tblHolidays in the field [tblHolidays.HolidayDate]

Output:
The number of transit days PLUS any holidays/weekends SUBTRACTED from [DueDate]

Example:
WorkDays(12/28/2015, 3, [tblHolidays.HolidayDate]) would return 12/22/2015 Because the Due Date is 12/28/2015, but 3 days are required for transit and 12/25 is omitted as a holiday while 12/26 and 12/27 are omitted as weekend

How's this ?

Public Function LatestShipDate(DueDate As Date, TransitDays As Integer) As Date
    Dim i As Integer, z As Integer, tdate As Date
 
    tdate = DueDate
    i = TransitDays
    Do
        z = DCount("*", "tblHolidays", "HolidayDate = #" & Format(tdate, "mm/dd/yyyy") & "#")
        If z = 1 Then
           tdate = tdate - 1
        ElseIf Weekday(tdate, vbSaturday) < 3 Then
           tdate = tdate - Weekday(tdate, vbSaturday)
        Else
           i = i - 1
           tdate = tdate - 1
        End If
     Loop Until i = 0
    LatestShipDate = tdate
End Function

Best,
Jiri

 

[bryanm694]

Hello Solo712,

I am not knowledgeable enough in VBA to look at a function and see precisely what it is going to do; however, that definitely does look like what I am looking for! How exactly would I go about integrating this into my database? Ideally, I would like for it to run after a user inputs a date in the txtDueDate control.

I have been playing around with this all morning and cannot figure out to get your code to run. From what I understand, there are some limits as to what type of function can run on certain procedures?

Thank you both very much!

 

[bryanm694]

I could definitely be incorrect on this assumption, but I am going to speculate that part of my issue lies in the fact that I have 2 controls on the same form that I want to put OnGotFocus event procedures on.

Everything in the form worked correctly before I pasted your code into it; however, now that your code has been pasted and an OnGotFocus event has been created, both controls error and display this message:

"Procedure declaration does not match description of event or procedure having the same name."

If I remove your code, that message goes away and my other control works perfectly just like it used to. Is there a way that I can get both to work together?

 

[Solo712]

I could definitely be incorrect on this assumption, but I am going to speculate that part of my issue lies in the fact that I have 2 controls on the same form that I want to put OnGotFocus event procedures on.

Everything in the form worked correctly before I pasted your code into it; however, now that your code has been pasted and an OnGotFocus event has been created, both controls error and display this message:

"Procedure declaration does not match description of event or procedure having the same name."

If I remove your code, that message goes away and my other control works perfectly just like it used to. Is there a way that I can get both to work together?

Can you send a snap picture of the form and the name of the field or textbox (ScheduledShipDate ?) where the function result would appear ?

Jiri

 

[bryanm694]

Hopefully I uploaded this correctly.

My cursor is in the control that I want the result to be in. It is called [txtScheduledShipDate] EDIT: It just occured to me that cursors do not appear when you use the print screen function. The control [txtScheduledShipDate] is directly to the right of the label "Scheduled Ship Date". This is probably fairly obvious, but I just wanted to clarify.

The user enters a value in the [txtDueDate] control. I would like your code to be carried out on the AfterUpdate event of the [txtDueDate] control.

The [txtDueDate] control has an OnGotFocus event that pulls the TransitDays value from tblCustomers. The control that this value is placed in is called [txtTransitDaysDetail] I left it visible so you could see it.

It may also be worth noting that the [cboProductID] control has an AfterUpdate event as well as an OnNotInList event. AT LEAST the AfterUpdate event fails when I try to put your code where I want it.

IGNORE the Actual Ship Date control. It is user entered and has nothing to do with any of the code I am trying to have in this form.

I am open to making any changes on this form if you think any are necessary.

Your help in this matter is very greatly appreciated. Hopefully I provided enough information.

 

[Solo712]

Hopefully I uploaded this correctly.

My cursor is in the control that I want the result to be in. It is called [txtScheduledShipDate] EDIT: It just occured to me that cursors do not appear when you use the print screen function. The control [txtScheduledShipDate] is directly to the right of the label "Scheduled Ship Date". This is probably fairly obvious, but I just wanted to clarify.

The user enters a value in the [txtDueDate] control. I would like your code to be carried out on the AfterUpdate event of the [txtDueDate] control.

The [txtDueDate] control has an OnGotFocus event that pulls the TransitDays value from tblCustomers. The control that this value is placed in is called [txtTransitDaysDetail] I left it visible so you could see it.

It may also be worth noting that the [cboProductID] control has an AfterUpdate event as well as an OnNotInList event. AT LEAST the AfterUpdate event fails when I try to put your code where I want it.

IGNORE the Actual Ship Date control. It is user entered and has nothing to do with any of the code I am trying to have in this form.

I am open to making any changes on this form if you think any are necessary.

Your help in this matter is very greatly appreciated. Hopefully I provided enough information.

Ok, no problem putting the call to the function in the After_Update for the txtDueDate but you also need to specify number of transit days or is it a constant ? In case it's a constant the code in the after update would be

If isNull(txtDueDate) Then Exit Sub 
 
ScheduledShipDate = LatestShipDate(txtDueDate, 3)

Provided that there is date in DueDate, the Scheduled Ship Date field shoud now be showing. If the number of transit days is variable you need to have another field on the form where you can specify it, and then supply the name of the field as a secund argument to the function.

Good luck.

Best,
Jiri

 

[bryanm694]

We are so very close to having exactly what I am asking for. I just have a question about your code

How's this ?

Public Function LatestShipDate(DueDate As Date, TransitDays As Integer) As Date
    Dim i As Integer, z As Integer, tdate As Date
 
    tdate = DueDate
    i = TransitDays
    Do
        z = DCount("*", "tblHolidays", "HolidayDate = #" & Format(tdate, "mm/dd/yyyy") & "#")
        If z = 1 Then
           tdate = tdate - 1
        ElseIf Weekday(tdate, vbSaturday) < 3 Then
           tdate = tdate - Weekday(tdate, vbSaturday)
        Else
           i = i - 1
           tdate = tdate - 1
        End If
     Loop Until i = 0
    LatestShipDate = tdate
End Function

Best,
Jiri

In the line that says "ElseIf Weekday(tdate, vbSaturday) < 3 Then"

What exactly does the < 3 mean? Does it mean that the minimum transit days MUST be 3? I wanted to get clarification on this before I attempt to alter it. We have a few customers that have a TransitDay value of 1 or 2. When I pick those customers and then run your code using 3/27/15 as my duedate, I get a txtScheduledShipDate value of 3/24/15 when I should be seeing a value of 3/26/15

 

[Solo712]

We are so very close to having exactly what I am asking for. I just have a question about your code



In the line that says "ElseIf Weekday(tdate, vbSaturday) < 3 Then"

What exactly does the < 3 mean? Does it mean that the minimum transit days MUST be 3? I wanted to get clarification on this before I attempt to alter it. We have a few customers that have a TransitDay value of 1 or 2. When I pick those customers and then run your code using 3/27/15 as my duedate, I get a txtScheduledShipDate value of 3/24/15 when I should be seeing a value of 3/26/15

No, the Weekday < 3 with a vbSaturday as the second argument means that Saturday is 1 and Sunday 2, i.e. the line subtracts that many days.

For the TransitDays, my bad, I did not see that on the order form (I was rushing out). All you need to do is to change the second line in the After Update Sub to

ScheduledShipDate = LatestShipDate(txtDueDate, TransitDays)

It should then work ok.

Best,
Jiri

ETA, I am seeing now that there may be a problem with having "TransitDays" both as an argument to the function and a name of a field. Let me know and we'll fix that.

 

[bryanm694]

Boy, that's embarrassing. I was positive that I changed that line of code when you posted it originally because you even said to. Obviously I did not change it like I thought I had.

I changed it to the name of the transitdays control and it still works; however, it is still not bringing up the correct value unfortunately. It appears as though it ONLY takes into account the transit days and nothing else (weekends, etc).

For example, If I enter a duedate of 3/30 (which is a Monday) your code returns 3/29 (which is Sunday) it should return 3/27 (Friday).

Edit: After playing around with some other customers of varying transit days, it seems to only have an issue with customers whose transitday value is 1. Every other transitday value seems to work correctly

Edit2: I am just speculating but it appears as though your code considers Friday and Saturday as the "weekend" days rather than saturday and sunday. But only on customers with a transitday value of 1 I could be wrong, but that's what appears to be happening.

I say this because if I enter a shipdate of 3/29, which is sunday, and will never ACTUALLY happen at our company; I get a return value of 3/26, which means that Friday and Saturday are added to the transitdays