Hi,
I'm working in Access 2000. I have a form with a series of date fields, showing the progress of a project from start to completion. There's a set of fields/controls for projected dates (when the project is projected to enter each stage of the process) and another set for actual dates. For my purposes, I'm only working with the projected dates.
There is a formula, based on the size of the project (how many hours it will take), for how many days each step of the process will require. I'm trying to set up the code so that when the projected completion date is entered, and based on the number of hours that has been entered, all the other projected dates will fill in automatically.
So in other words, if someone enters a project that will take 525 hours with a projected completion date (CompletionPJ) of 6/25/09, the database will automatically fill in the projected start date as [CompletionPJ] - 60 (and will fill in all the other projected dates in the middle according to their respective equations).
I've figured out the easy part of this (code is below), but I'm trying to figure out how to get it to count only work days. For instance, in the formula above, it would be [CompletionPJ] - [60 work days, not counting weekends or holidays].
I have a separate calendar table with CalendarID (autonumber), CalendarDate, CalendarDate2, isWorkDay, and isHoliday. (CalendarDate and CalendarDate2 are identical date fields; they make it possible for me to find the number of working days between two dates for other reports in the database.) isWorkDay and isHoliday are yes/no (all weekend dates are false in isWorkDay; weekday holidays are marked true in isHoliday). So I'm trying to figure out how to tell Access to subtract from [completionPJ] a certain number of days where isWorkDay = True and isHoliday = False.
At this point I don't have the Calendar table included in the query for this form, but I'm guessing I'll need to add it (unless it's possible to make VB code check info in a table that's not part of the data source for the form??).
Here's the code I have so far: - Private Sub txtCompletionPJ_AfterUpdate()
-
-
If Not IsNull(Me![txtHours]) Then
-
-
Select Case txtHours
-
-
Case 1 To 499
-
Me!txtStartPJ.Value = Me!txtCompletionPJ - 30
-
-
Case 500 To 999
-
Me!txtStartPJ.Value = Me!txtCompletionPJ - 60
-
-
Case 1000 To 1499
-
Me!txtStartPJ.Value = Me!txtCompletionPJ - 90
-
-
Case 1500 To 1999
-
Me!txtStartPJ.Value = Me!txtCompletionPJ - 120
-
-
Case Is >= 2000
-
Me!txtStartPJ.Value = Me!txtCompletionPJ - 180
-
-
End Select
-
-
End If
-
-
End Sub
In all there are about 12 steps in the process, so there will actually be a dozen lines for each case.
Anybody have an idea to help me out?
Thanks,
Angi
2 2803
Hi, there are a few examples of workdays functions around if you google ms access workdays function, you could also look at [HTML]http://www.mvps.org/access/datetime/date0012.htm[/HTML]
Also here's a couple of functions that I've borrowed and/or reworked a few times. From what I remember I had problems with the first one, where I couldn't get it to infallibly return the correct number of days (when compared against manual counts), but I use the second one regularly without probs. I know I adapted this from one I saw elsewhere, until it worked for me in my particular context so you may also need to fiddle with it.
One thing, when I first tried to solve this same issue I spent hours and hours fighting with the original functions trying to get them to work properly - the major problem (I subsequently realised) was that I needed to control for nulls within the query formula as well as or instead of from within the formula, otherwise it just locked up and hung. Just a tip that might save you some pain!
HTH
Kevin - Option Compare Database
-
Option Explicit
-
-
Function funWorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
-
'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
-
'This function calculates the number of workdays between two dates.
-
'The number of workdays is inclusive of the beginning and ending dates.
-
'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
-
'The table must list the dates of holidays to be observed.
-
'The user may include other fields in that table, for example, a description of the holiday being observed.
-
-
Dim lngTotalDays As Long
-
Dim lngTotalWeeks As Long
-
Dim dtNominalEndDay As Date
-
Dim lngTotalHolidays As Long
-
-
'Check to see if dtStartDay > dtEndDay. If so, then switch the dates
-
If dtStartDay > dtEndDay Then
-
dtNominalEndDay = dtStartDay
-
dtStartDay = dtEndDay
-
dtEndDay = dtNominalEndDay
-
End If
-
'Here are how many weeks are between the two dates
-
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
-
'Here are the number of weekdays in that total week
-
lngTotalDays = lngTotalWeeks * 5
-
'Here is the date that is at the end of that many weeks
-
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
-
'Now add the number of weekdays between the nominal end day and the actual end day
-
While dtNominalEndDay <= dtEndDay
-
If WeekDay(dtNominalEndDay) <> 1 Then
-
If WeekDay(dtNominalEndDay) <> 7 Then
-
lngTotalDays = lngTotalDays + 1
-
End If
-
End If
-
dtNominalEndDay = dtNominalEndDay + 1
-
Wend
-
'Here are how many holiday days there are between the two days
-
lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= #" & dtEndDay & "# AND dtObservedDate >= #" & dtStartDay & "# AND Weekday(dtObservedDate) <> 1 AND Weekday(dtObservedDate) <> 7")
-
-
'Here are how many total days are between the two dates - this is inclusive of the start and end date
-
funWorkDaysDifference = lngTotalDays - lngTotalHolidays
-
-
End Function
- Public Function fWorkDays(StartDate As Variant, EndDate As Variant) As Variant
-
'Public Function fWorkDays(StartDate As Date, EndDate As Date) As Long
-
On Error GoTo Err_fWorkDays
-
-
Dim intCount As Integer
-
Dim rst As DAO.Recordset
-
Dim DB As DAO.Database
-
-
Set DB = CurrentDb
-
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
-
-
'StartDate = StartDate + 1
-
'To count StartDate as the 1st day comment out the line above
-
-
intCount = 0
-
-
Do While StartDate <= EndDate
-
-
If Not IsNull(StartDate) And Not IsNull(EndDate) Then
-
rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
-
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
-
If rst.NoMatch Then
-
intCount = intCount + 1
-
End If
-
End If
-
End If
-
-
StartDate = StartDate + 1
-
-
Loop
-
-
fWorkDays = intCount
-
-
Exit_fWorkDays:
-
Exit Function
-
-
Err_fWorkDays:
-
Select Case Err
-
-
Case Else
-
MsgBox Err.Description
-
Resume Exit_fWorkDays
-
End Select
-
-
End Function
Thanks for the ideas. I googled and found a function that does work in theory, but now I'm trying to figure out how to connect this function/module to the code in my form. I haven't worked with functions a whole lot, so I'm still figuring this out.
Here's the function (based on http://www.tech-archive.net/Archive/Access/microsoft.publi c.access.forms/2008-10/msg00851.html): - Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
-
Dim lngAdd As Long
-
Dim lngDayCount As Long
-
-
On Error GoTo AddWorkDays_Error
-
-
If DaysToAdd < 0 Then
-
lngAdd = -1
-
Else
-
lngAdd = 1
-
End If
-
-
AddWorkDays = OriginalDate
-
-
Do Until lngDayCount = DaysToAdd
-
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
-
If Weekday(AddWorkDays, vbMonday) < 6 Then
-
If IsNull(DLookup("[holidate]", "HolidaysTable", "[holidate] = #" & _
-
AddWorkDays & "#")) Then
-
lngDayCount = lngDayCount + lngAdd
-
End If
-
End If
-
Loop
-
-
AddWorkDays_Exit:
-
On Error GoTo 0
-
Exit Function
-
-
AddWorkDays_Error:
-
MsgBox "Error " & Err.Number & " (" & Err.Description & _
-
") in procedure AddWorkDays of Module modDateFunctions", vbExclamation, conMsgTitle
-
GoTo AddWorkDays_Exit
-
-
End Function
In my form, txtCompletionPJ corresponds to OriginalDate in the function, and for each date (such as txtStartPJ or txt2ndStepPJ) in each Case I have to specify a certain (negative) number to correspond to DaysToAdd in the function. The end result of the function, AddWorkDays, corresponds to the date I'm trying to find (such as txtStartPJ or txt2ndStepPJ).
Here's another example of the code in my form (two cases with two controls each for now). Can you give me any guidance on how I connect the function to the form? I've tried but I haven't figured it out. -
Option Compare Database
-
Option Explicit
-
-
Private Sub txtCompletionPJ_AfterUpdate()
-
-
If Not IsNull(Me![txtHours]) Then
-
Select Case txtHours
-
Case 1 To 499
-
Me!txtStartPJ.Value = Me!txtCompletionPJ - 30
-
Me!txt2ndStepPJ.Value = Me!txtCompletionPJ - 25
-
Case 500 To 999
-
Me!txtStartPJ.Value = Me!txtCompletionPJ - 60
-
Me!txt2ndStepPJ.Value = Me!txtCompletionPJ - 53
-
End Select
-
End If
-
-
End Sub
Thanks,
Angi
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Larry Woods |
last post by:
I have a site that works fine for days, then suddenly, I start getting ASP
0115 errors with an indication that session variables IN SEPARATE SESSIONS
have disappeared!
First, for background information, I have a customized 500-100 page that
sends the value of various session variables via email to my support site.
The situation:
On the home page of the site, the FIRST THING that is done is a Session
|
by: wireless200 |
last post by:
I've got a table with some datetime fields in it.
One field (call it field 1) is of the form mm/dd/yyyy and the other two
(fields 2 and 3) are in the form of hh:mm:ss:xx where xx is hundreths
of a second.
I'm getting the difference between field 2 and 3 using (datediff(ms,
access_time, release_time )/1000/60.). This seems to work fine.
However, in some other cases I'd like to add field 1 to field 2 and
|
by: BlackFireNova |
last post by:
I need to write a report in which one part shows a count of how many
total records fall within the working days (Monday - Friday) inside of a
(prompted) given date range, in a particular geographical region.
I have written a query which prompts the user for the start and end
dates. It also filters for entries which pertain to the particular
geographical region.
I'm not sure where to go from here.
|
by: Rob R. Ainscough |
last post by:
I'm using Visual Studio .NET 2003 (Visual Basic) and I can't seem to get my
Task List to update automatically when I enter comment token:
' TODO: This is some stuff to do
According to documentation this should automatically add to the Task List,
but it doesn't?? I've checked under my Tools, Options, Environment, Task
List and the TODO entry is there. But what I find odd is that at dialog I
can NOT "Add" and comment tokens? I know...
|
by: Jason Huang |
last post by:
Hi,
In our C# Windows Form application, we are using the SQL Server 2000 as the
database server.
The Database table MyTable has a field RegistrationDate which represents the
Date a client comes to our company to deliver his product. We have some
Working days for processing his product, like 20, 30, 40 days.
My question is how do I handle the working days problem, e.g., a client
comes at the Days February 15 where we have 40 Working...
| |
by: paulmac106 |
last post by:
Hi.
I utilize the Calendar table, and I'm able to find how many working
days between 2 dates, but does anyone use this table to find the 2nd or
5th working date?
if 11/30/06 then 12/4/06
I'm sure it's not too difficult but i can't seem to get it to work
|
by: Santiagoa |
last post by:
If I set up a task table with an Date_assigned and a number of days to complete the task I calculate the end_date field by using the code below I found in this forum
How ever when I enter the Date_assigned and update the record, nothing happens until I manually enter a value in the DaysToComplete field. I want to keep DaysToComplete Constant (5 Days) so I tried to set the attribute in the table with 5 as the default but this does not work....
|
by: Basildk |
last post by:
Hi.
I have a strange problem. We have an asp.net application running on
several server with different setups.
On 2 of our servers we experience that the globalization settings are
misbehaving.
We have boiled the problem down to this: (exemplified by a very simple
page)
|
by: Maninder Karir |
last post by:
Hi,
Im trying to work out how to return a date based on addition of working days only.
On the userform the user enters the date using dtpicker. They then have the option to add 5, 10 or 15 days. I need this to then return the date, adding only the working days, in a new Text box.
Any deas?
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |