+ 7 Working Days Date Function

[JPW]

Current code:

If Me.Tick7Day Then
Me.Text7Day = Date()
Else
Me.Text7Day = ""
End If

Okay, I have a check box and a text box on my form, but I need some assistance with complex VB code to make something work. At the moment if the check box is TRUE then the current date will be displayed in the label box. However, what I now need is code to make this box display the date + seven working days.

I've tried to look at various Internet sites, but they mostly talk about working days between two dates and that's not really what I want.

Basically letters are sent out that expire seven working days from the sent date and need that information within the database.

Many thanks.

*edit*

I'm from the UK so it must be UK complaint with UK working day dates etc etc.

 

[RuralGuy]

Here's one you can use. Put it in a standard module.

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

 

[JPW]

So I put that into a module, but how would I incorporate the code in the form to use this new module code? Obviously the below will need to be modified. (Sorry, just new to this VB stuff).

If Me.Tick7Day Then
Me.Text7Day = Date()
Else
Me.Text7Day = ""
End If

 

[RuralGuy]

If Me.Tick7Day Then
   Me.Text7Day = Cstr(PlusWorkdays(Date(),7))
Else
   Me.Text7Day = ""
End If

 

[JPW]

I got problems.

I have put the code into the module caled 'Workdays', but am right to assume that because I have no holiday table that I can take some code out of it? Thus it looks like this:

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
intNumDays = intNumDays - 1
End If
Loop
End Function

I then have put in the other code into the after update part of the form:

If Me.Tick7Day Then
Me.Text7Day = Cstr(PlusWorkdays(Date(),7))
Else
Me.Text7Day = ""
End If

Howecer the debugger pops up and I get the '.Tick7Day' with blue background in the VB code window and a yellow background highlighted highlighted 'Private Sub TickSeven_AfterUpdate()'

*edit*

If it's more accurate to do a holiday table then I may go down that path, but what do I need in the table?

 

[RuralGuy]

I have put the code into the module caled 'Workdays', but am right to assume that because I have no holiday table that I can take some code out of it? Thus it looks like this:

The following function is correct!
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
intNumDays = intNumDays - 1
End If
Loop
End Function

I then have put in the other code into the after update part of the form:

If Me.Tick7Day Then
Me.Text7Day = Cstr(PlusWorkdays(Date(),7))
Else
Me.Text7Day = ""
End If

Howecer the debugger pops up and I get the '.Tick7Day' with blue background in the VB code window and a yellow background highlighted highlighted 'Private Sub TickSeven_AfterUpdate()'

...so what is the real name of your control?
*edit*

If it's more accurate to do a holiday table then I may go down that path, but what do I need in the table?
...just a date field that you wish to declare a holiday.

 

[JPW]

Sorry, my bad.

I had the wrong names in the code.

Thank you so much - the code works great.

However, I'm interested for accuracy reasons and more inclined to have a table. Obviously as you stated in the code I'll have to use the different if statement in the VB code.

I assume looking at your code that the table name will be 'tblHolidays' and the field in it wil be called 'HolDate' and I all I do is enter the date of a holiday and that's pretty much it?

So the new code will be:

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 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

 

[RuralGuy]

And the HolDate field should be a DateTime field. This scheme requires dates for each year you will be running.

 

[JPW]

Getting there I think, but slight problem.

I use the following code in the module:

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 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
intNumDays = intNumDays - 1
End If
Loop
End Function


table name; tbl Holidays
Field Name: HolDate

However, I get a message stating 'run time error 2471' The Expression you entered as a query parameter produced this error [Holiday].

Is there meant to be another field called holiday?

Thank you for your time in helping me.

 

[KeithG]

Yeah, from the looks of the Dlookup function there need to be a field in tblHoliday called Holiday. I would assume it would contain the name of the holday (ie christmas, easter)

 

[JPW]

Okay, everything seems to be going right. Forward my computer's clock and trialed various dates and they appear to be working.

Thank you for assistance.

 

[rntkid]

PlusWorkdays Runtime Error

Hi Rural Guy,

I've tried to get the PlusWorkdays module with the holiday table to work, but I get a "Runtime Error: ‘2001’ You cancelled the previous operation" everytime, which freezes the application.

I'm using this module, which I copied from another website:

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 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

I am calling it from a query using: 2DayDue: PlusWorkdays([WeekdayRec'd],2). I have a table called "tblHolidays" with two fields: the date/time field, "HolDate" and the text field "Holiday".

I started this database a year ago, and this is the only thing I can't figure out. Any help would be most appreciated...

 

[RuralGuy]

Is WeekdayRec'd a DateTime field? It is a bad idea to use an apostrophy " ' " in a name. What is the name of the standard module where you put the PlusWorkdays function?

 

[rntkid]

Hi RG,

Thank you so much for responding! I actually don't have the apostrophe in the field name, just a force of habit when I typed my question (sorry!). The "WeekdayRecd" field is a date/time field. I named the standard module "basFunctions", as you suggested.

Thank you again,
rntkid

 

[RuralGuy]

Can you set your system to Break on All Errors and tell us which line is causing the problem? While viewing the code go to Tools>Options...>General Tab

 

[rntkid]

I set it to "Break on All Errors," but I'm not sure what I'm supposed to do next. I tried running the query again and the same thing happens: The same three lines of the module are highlighted in yellow with a yellow arrow in the left margin pointing to the last line.

If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then

 

[RuralGuy]

Are you sure you have everything spelled correctly for your tblHolidays table? When you hover over PlusWorkdays, what date appears? It could be the incoming data.

 

[rntkid]

I've checked my tblHolidays again, just to make sure, and everything appears to be correct. I hovered over the PlusWorkdays to see what the date is. It said, "PlusWorkdays = 6/6/2006" (quotation marks are mine). Since I'm pretty ignorant about VBA, I have no idea what to make of that. The first record in the field "WeekdayRecd" is the date "6/5/2006." If I run the query without the holiday part of the module, it works fine. After all the lengthy modules I've seen about how to skip weekends, it's amazing how short and simple yours is! Now if only the holiday part of it would work. If you can help me with this, I'll be able to apply it to the two other due date fields (there are a total of three due dates). Right now I'm using a very cumbersome workaround involving a separate Due Dates table with the WeekdayRecd field as a "lookup" field in the main table. The three due dates are filled in for every workday (excluding weekends and holidays). I had all the dates worked out in Excel calendars using formulas and a worksheet with all the holidays on it.

Thanks so much for trying to help me with this.

 

[RuralGuy]

The error you are getting is one that usually means the DLookup() was called with a bad argument. Any chance you can post your db so we can take a look at it? Another option would be to duplicate the function and delete the first one in case there is some sort of corruption in it.

 

[rntkid]

I would love to have you take a look at it. The records in the database are confidential, so I'm going to make a copy, delete the records and make up some fake records up so you can see it in action. I'll post it then, but can you tell me how I go about posting a file?