Calculating one date from another excluding sundays (1 Viewer)

[MICHELE]

I'm not sure exactly where to start on this one. I've searched previous threads and haven't found anything that seemed like it would really help.

I need to calculate a LoadDate from a DeliveryDate based on the number of days associated with that State that it is to be delivered to.

Each of the 50 states has a number from 0-14 which is the number of days it takes to deliver a job to that state. Actually right now there are only 5 numbersj: 1,2,3,4,14, But this could, and probably will change so I wanted to make it easy to add a new number (probably up to 15).

We do not ship on Sundays, nor do we count Sunday in the calculation of days. So if it takes 4 days to ship to a state and the DeliveryDate entered is on a Friday, the LoadDate should calculate to the following Wednesday: Friday + Sat(1) + Mon(2) + Tues(3) = Wed(4th day).

Now to complicate things even more, holidays are also excluded. I have a seperate table with holiday dates that we use at our company.

Can someone help me? I'm not sure where to start.
Thank you in advance for any help!!

 

[Mile-O]

Search for posts by Blackwidow that I've helped on - I've done loads on excluding days, holidays, etc.

 

[MICHELE]

Thank you! I found one post that really helped. I've changed the code some to adapt to what I need and I'm getting an error message. This is the first time I've ever written a module outside of my form. I just do Access part time here so I've tried to make my db pretty simple, but they just keep wanting more so I'm really trying to get better with VBA. I just need a little help in addition to all my books. This website is great!

Anyway, I think I'm going to have 2 modules to make it as simple as possible for me to uderstand:
1 to calculate [tentativeLoadDate] and another one to calculate [LoadDate] based on [tentativeLoadDate].

Each one of these dates excludes different days so that's why I'm making 2 modules. [tentativeLoadDate] calculates based on [DELIVERYDATE] + [DAYSTODRIVE] and excludes Sundays and driving holidays, [drivingHolidays]
Then for the next one I need the [LoadDate]. My company won't actually load something on Saturday or Sunday so I need to exclude Saturday as well as Sunday in this module and also [scheduleHolidays] are sometimes different than [drivingHolidays].

I have a seperate table that has our holidays and the form that will be using the module(s) actually is based on 2 queries so that entry would be allowed using all the different tables.

So far I've only gotten partially through one of my modules, the one to calculate [tentativeLoadDate]. I seem to be having a problem defining [drivingHolidays] because it's a boolean.

Anyway here it is. I know my naming procedueres have a lot of indescrepencies so please forgive me. I've tried to do better, but can't really, or don't really have the time to go back and correct them now that I know better.

Module 1:

Option Compare Database
Option Explicit

Function CalculateTentativeLoadDate(ByVal DELIVERYDATE As Date, ByVal DAYSTODRIVE As Integer)

'EXCLUDE SUNDAYS, AND DRIVING HOLIDAYS

On Error GoTo Err_CalculateTentativeLoadDate

Dim intCounter As Integer, intTotal As Integer, drivingHoliday As Boolean, holdate As Date

For intCounter = 0 To (DELIVERYDATE - DAYSTODRIVE)
If (WEEKDAY(DELIVERYDATE - intCounter) = 1) Or _
(WEEKDAY(DELIVERYDATE - intCounter) = holdate And drivingHoliday = Yes) Then
' do nothing
Else
intTotal = intTotal - 1
End If
Next intCounter

CalculateTentativeLoadDate = DELIVERYDATE - intCounter

Exit_CalculateTentativeLoadDate:

Exit Function

Err_CalculateTentativeLoadDate:
MsgBox Err.DESCRIPTION, vbExclamation, "Error #" & Err.NUMBER
Resume Exit_CalculateTentativeLoadDate

End Function

And this is what I wrote in the onClick of my command button in my form:
Me.txtTentativeLoadDate = CalculateTentativeLoadDate.CalculateTentativeLoadDate([DELIVERYDATE], [DAYSTODRIVE])

I've tried to make this clear without writing too much unneeded information. Any help is really appreciated!


My error is "variable is not defined" and it highlights the Yes so I tried True and 0 instead and got an overflow error message with both of those solutions.

 

[Mile-O]

Change Yes to True.

 

[MICHELE]

Changing yes to true gives me an overflow error.

 

[Mile-O]

Currently you are not using all the variables you are dimensioning - I remove them.

Public Function CalculateTentativeLoadDate(dteTemp As Date, intDays As Integer) As Date
    On Error GoTo Err_CalculateTentativeLoadDate
    Do While Not intDays = 0
        dteTemp = DateAdd("d", 1, dteTemp)
        If (Not Weekday(dteTemp) = 1) Then _
            intDays = intDays - 1
    Loop
    CalculateTentativeLoadDate = dteTemp
Exit_CalculateTentativeLoadDate:
    Exit Function
Err_CalculateTentativeLoadDate:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CalculateTentativeLoadDate
End Function

 

[Mile-O]

You would get an overflow, so you know, because you were subtracting a tiny number from a date (i.e. Today - 5)

mathematically, this is 38029 - 5 and 38024 is beyond the scope of the integer variable to which you were assigining it.

 

[MICHELE]

Thank you, thank you , thank you!!!

Mile-O-Phile you've been a ton of help!

Now I'm going to work on getting that driving holiday in there.