Another weekdays question

[rcappell]

I have a database (big surprise ) and I need it to calculate and filter information based on a process name, a number of days for that process and a schedule date.

For the process NC Batch I need it to calculate out 15 working days from today excluding weekends and holidays and then find the records in the orders table that has a schedule date less than or equal to the returned date. I have a calendar in excel that does it with a look up.

I have a table listing holiday dates and a table listing processes and the corresponding day count.

In the query I currently have it listed as

[ScheduleDate]<=Date()+20.

but this will not take into account holidays and it includes weekends.

I know there are several threads on work day functions but I could not get any of them to adapt to my needs. Sorry.

 

[RuralGuy]

In your query you would have: [ScheduleDate]<=PlusWorkdays(Date(),15)


And in a Standard Module named basFunctions:

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
     PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
     If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
'     If Weekday(PlusWorkdays, vbMonday) <= 5 And _
      IsNull(DLookup("[Holiday]", "tblHolidays", _
      "[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
      '-- The above Format of PlusWorkdays works with US or UK dates!
          intNumDays = intNumDays - 1
     End If
Loop
End Function

 

[rcappell]

Okay I did what you instructed, but I get this message when I run the query:

Run-time error '3075':

Syntax error (missing operator) in query expression '[HolDate] =
#10/28/2006#:::Null'.

 

[RuralGuy]

You need to copy and paste what I posted. Those are semi-colons and not colons!

 

[rcappell]

Okay I had a brain hiccup, right after I sent the post I realized what I had done. I apologize and I thank you for the assistance.

 

[RuralGuy]

No problem. Glad to help.