Calculated a "Need to ship by [date]" based on [DueDate] minus [TransitDays]

[bryanm694]

The title pretty much says what I need to do. 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

 

[Joe4]

Check out the code created here: Date/Time: Doing WorkDay Math in VBA

 

[bryanm694]

Thank you very much. I am not sure how I did not come across this earlier. At a glance, it looks like I *might* be able to modify that first function in that link. I am playing around with it right now. I will post an update once I either figure it out or get stuck.

Thank you again.

 

[bryanm694]

This is where my lack of knowledge in VBA is really going to show but I took that first function and modified it into this

Option Compare Database


' ********* Code Start **************
'
' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
'


Public Function dhAddWorkDaysA(TransitDays As Long, _
Optional DueDate As Date = 0, _
Optional HolidayDate As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

It returns a #Name error in the ScheduledShipDate field. I am not sure why

Edit: This is the calculation that I have the ScheduledShipDate control based on:

=IIf(IsNull([tblOrderDetails.TransitDays]),Null,(dhAddWorkDaysA([tblOrderDetails.TransitDays],[DueDate],[tblHolidays.HolidayDate])))

 

[Joe4]

Did you copy ALL the VBA code in that link?
Note that some of the UDFs call other UDFs in that link (they interact with each other). So make sure that you copy all of it.

 

[bryanm694]

Alright, I copied all of the vba from start to finish. It still returns a #Name? error. Any suggestions?

 

[Joe4]

This is not the proper way to reference table/fields:
[tblOrderDetails.TransitDays]

It should look like this:
[tblOrderDetails].[TransitDays]

 

[bryanm694]

Forgive me, I typed that calculation very quickly before lunch. I have fixed it; however, the same error still stands.

I will continue to look at it very closely and see if I am missing something obvious like that