Forms :: Calculate Passed Working Days And Show Them In A Form
Jul 8, 2015
I need to calculate the passed working days and show them in a form. I should be also able to use the number in a query later on. in excel I use the formula to get the days passed:
Code:
=IF(ISERROR(MATCH(F10,Dictionary!C:C,0)),NETWORKDAYS(D10, TODAY(), Dictionary!$E$2:$E$43),"Status Excluded")
D = "Date_uploaded" in access table "tbl_All_Cases"
F = "Status_Case" in access table "tbl_All_Cases"
c:c = dictionary case status
E:E = dictionary holidays
"Status Excluded" will show up in the cell if a case has one of the status from the dictionary..I created a table: tbl_Dictionary where there are 2 fields: "Case_Status" and "Holidays".How can I translate the above formula into something that access will understand?
I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.
Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.
'*********** Code Start ************** Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1 'If you want to count the day of StartDate as the 1st day 'Comment out the line above
intCount = 0 Do While StartDate <= EndDate 'Make the above < and not <= to not count the EndDate
Select Case WeekDay(StartDate) Case Is = 1, 7 intCount = intCount Case Is = 2, 3, 4, 5, 6 intCount = intCount + 1 End Select StartDate = StartDate + 1 Loop WorkingDays = intCount
Exit_WorkingDays: Exit Function
Err_WorkingDays: Select Case Err
Case Else MsgBox Err.Description Resume Exit_WorkingDays End Select
I hope you can help. I have a database and I am trying to work out the working days between the start date and the end date of a programme. This will not take into account any weekends or bank holidays. I have created a separate table called tblHolidays which has a list of the Bank Holidays. The majority of it works but out of 1700 records 50 don’t. These are ones in which it starts on 1 month finished on another or has a bank holiday. It works with certain bank holidays but not all of them. Bank holidays go back to the beginning of 2006 The following is the code I have used: Public Function WorkingDays2(Start_Date As Date, End_Date As Date) As Integer
On Error GoTo Err_WorkingDays2
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)
Start_Date = Start_Date + 1
intCount = 0
Do While Start_Date <= End_Date
rst.FindFirst "[HolidayDate] = #" & Start_Date & "#" If Weekday(Start_Date) <> vbSunday And Weekday(Start_Date) <> vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If
Start_Date = Start_Date + 1
Loop
WorkingDays2 = intCount
Exit_WorkingDays2: Exit Function
Err_WorkingDays2: Select Case Err
Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select
End Function
I then used the SQL
Select WorkingDays2 ([start_Date], [End_Date]) As CountDays
I am using workdays to calculate time passed between two dates. I also have a table for Holidays that I don't want counted as work days. This is working well, but I would like my queries to show a null value instead of showing #Error when a date field is empty. Here is my coding in my Module.
Option Compare Database Option Explicit Public Function Workdays(ByRef startDate As Date, _ ByRef endDate As Date, _ Optional ByVal strHolidays As String = "Holidays" _ ) As Integer
I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?
The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.
How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks
Tim
Below is the code i'm using for defining Regular days and Sunday Working Days:
Private Sub LogDate_AfterUpdate() Dim rst As DAO.Recordset, rs As DAO.Recordset Dim db As DAO.Database Dim dteToday As Date, dtePrior As Date Dim i As Integer
Set db = CurrentDb Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
rst.FindFirst "[HolidayDate] = #" & dteToday & "#" 'See if the date entered is a holiday If rst.NoMatch Then Holiday.Value = 0 Else Holiday.Value = 1 End If If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if Dim strSQL As String 'it will be a regular day or a Sunday
I rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
Hi I have a Text box that stores a date of when a problem or query is received on to a customer account, when resolved the date it is resolved is inputted in the next text box as a resolved date. My third Text box then needs to display Yes or No depending if it took less than 28 days to resolve.
Ive used a Text box and DateDiff expression to work out the ammount of days in the control source, Called query dauration then a macro to test if its less than 28 days to display the yes or no. But this is not working days Mon - Fri. Has any one any ideas?
Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !
I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.
I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.
Suggestions please !
Steve
Private Sub cmdDate_Click() Dim FirstDate As Date ' Declare variables. Dim Number As Integer Dim dtEndDay As Date Dim dtinterimday As Date Dim intcount As Integer Dim lnginterimdate As Long Dim intdays As Integer
Dim Msg FirstDate = InputBox("Enter a date - dd/mm/yy") Number = 20
dtinterimday = FirstDate intdays = Number Do Until intcount = Abs(intdays)
If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1
lnginterimdate = dtinterimday
If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then
intcount = intcount + 1 End If End If
Loop
Msg = "New date: " & dtinterimday MsgBox Msg End Sub
I am trying to calculate the working days Based on all web searches I am unable to find the specific scenario I am working in Auto industry, which means auto industry usually close twice a year for any reasons, let say in July for one or sometime two weeks and in December depends upon the Christmas date usually from December 20 till Jan 01
Now my question is when i am enter the holiday details in table do I have to enter line by line date e.g. july 01, 02, 03 (I am able to understand, how this works but still not yet tried) OR july 01 to July 07 (which make sense, but unable to find how to use date range to calculate working days)...
I am making a database for my work place where there is telephone counsellors and they need to complete a certain number of supervision hours and a few other categories of hours required for training and several other things. The manager wants to be able to see the total supervision hours and the other categories for a worker when the worker is selected and the date range for the queried time entered.
I have a form that has quite a few items on it. I have two text boxes that allow me to enter a start date and an end date, I have a combo box that allows me to select a worker and I have 3 text boxes that I want to populate with the sum of 3 separate columns in another table when the date is entered and the worker selected so the manager can see, at a glance, how many hours each worker has done on the separate items. Maybe I would need an update button at the bottom that, when clicked, would perform the required calculations.
I have 3 queries that return the required information but I need to get one of the columns from the query results put into each of the 3 text boxes. Basically, when I click on the buttons the right query appears, I just want column 3 (which is a sum column) to get put in the corresponding text box and I want all the boxes to display the sum of their corresponding queries when a date and person is selected.The form is called frmSearch, the 3 queries are called qry_sumisshours, qry_sumisshours and qry_sumtcshours. The 3 text boxes I need to get populated from the queries are called txt_ results_ sv_ hours, txt_results_TCS_hours and txt_results_iss_hours.
I have a big table, EquipmentDetails and separately I have job plans.Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.
I am generating a List Box in a form which is populated from a query.The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.
The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.What kind of SQL do I need to only grab the ones relating to the current JobID please?I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.
I have a continuous form it has a field (DateStart) and a field (DateStartCarryOver). There is an event on (DateStart) for the (DateStart.Value) to be passed to a new record in (DateStartCarryOver).
My probem is that the new record dosent get saved (i.e. it dosent get an ID so a record isnt created. How can I create that record once the data has bee pased to it?
In a database, I have the following date fields that I use to track projects that start and stop.
START1 STOP1 START2 STOP2 START3 STOP3
Is there a way I can use a query to calculate the total days the project was worked on? For example if I enter 1/1/06 in START1, 1/10/06 in STOP1, 2/1/06 in START2 and 2/15/06 in STOP2, 3/1/06 in START3 and 3/30/06 in STOP3, I need to calculate the total days. I need it to work regardless if there are 3 start/stops, 2 or just one start/stop.
Admit Date Discharge Date Report Begin Date Report End Date
I can calculate the number of days with the DateDiff function when the "Admit Date" and "Dischage Date" are within the range of the report with no problem.
The problem arises when the "Admit Date" is prior to the "Report Begin Date". In this case, the difference needs to be "Report Begin Date" and "Discharge Date".
To make this more complicated, in some cases there is no "Discharge Date", so the calculation needs to look at the first two senerio's and then calculate the days based on the "Report End Date".
Expr2: IIf([Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))
Expr2: IIf([Admission Date]<[Beginning of Report] And [D/C Date / Time]>[End of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))
Expr3: IIf(IsNull([D/C Date / Time]) And [Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[End of Report]),DateDiff("d",[Admission Date],[End of Report]))
expr4: IIf([Admission Date]>[Beginning of Report],DateDiff("d",[Admission Date],[D/C Date / Time],0))
Above are failed attempts to get the formula right.
Can anyone assist me in getting this to work correctly.
I have a date range of 1/01/2005 to 12/25/2005 for example. I need a formula to return the number of days between these dates excluding weekends. Basically I need to know how many business days are between these dates. Thanks for the help.
I am woking on a project full of nightmares. My latest, and the one I am currently unable to solve, involves trying to calculate the number of days between Billing Dates.
I have a table named "Date" - I didn't design this! It contains a field named "Date"
If I query for just that Field I get the following: Date 9/8/2005 8/8/2005 7/8/2005 6/8/2005 5/9/2005 ...
I need to figure out a way to determine the number of days between the bills so I can calculate the average cost per day.
It seems like it should be an easy thing using like the datediff function or something, but since they are in separate records (rows), I can't figure it out. I have tried and tried and tried.
I have a database with 10-12 fields containing dates certain things were completed. There is also a field for the 3 different locations we have and a program year 2006, 2007, or 2008. What they would like to do is click a button and get a list to choose the first date they which to compare, then a list to choose the second date, calculate the number of days between the two dates, select the program year from a list, and a location from a list. Can anyone help me with how to do this if it's possible?
The fields look something like this:
Prog Year Location DateA Date B DateC DateE 2006 G date date date date 2006 G date date date date 2006 P date date date date 2008 D date date date date 2007 P date date date date
For Example, I might want 2006 Program Year, Location P, calculating days between DateA and DateB, except that the next time I might want to choose a different set of criteria.
I have an access database used for recording training events. I was able to calculate the number of days each training event took by using
Datediff("d",[Training Date From],[Training Date To]+1)
I was really chuffed with this until my boss asked what happens when the training only lasts half a day! I added the time to the above fields to make them date/time fields but can't see a way to calculate in half days.
As well as this the other problem is that training could be over a few days. As an example I'd like to be able to input that the training started at 9am on Monday 29th May and ended at 12 noon on Thursday 1st June and get the result of 3.5 days. The full days would all end at 5pm but I wouldn't be recording that, just the total days. I tried changing the datediff function to hours in the hope I could somehow discount the non working hours but that didn't work! I'd ideally like the final result to be in days, ie 0.5, 1 etc. It would always be in whole or half day increments - don't need things to be overly complicated!
Any help will be very gratefully received. I'm definitely not an expert as you can probably tell!! :eek:
I'm trying to create a sub to count how many days between 2 dates excluding Holiday and weekend. Using the DateDiff() function is not allowing me to exclude certain days.
On one of my reports, I want to show the "Days Open". If the RC_Status is "Active" I want to calculate the "Days Open" by subtracting RC_START_DATE from Today(). However if the RC_Status is "Closed" I want the "Days Open" to be calculated by RC_APPROVAL_DATE minus RC_START_DATE.
So the question is, what is the best way to do this and how would I write the formula to calculate this?
I'm trying to step from Excel to Access and I have some issues to translate functions that I used in Excel to Access.This is how it looks in Excel.And I have issues to translate the following function in Excel to Access.
=IF(AND(I6=0),"Done",K6-A$4)
A$4 is related to cell where it states the function =now() Basically what I want is to calculate in the tablecolumn is the following.In the column "Days remaining" I want the remaining days from "date to pay" and today. If the amount states "0" then the outcome must be "done".
I have a contract management database where contracts have a start date and an end date. I also have a date parameter set up whereby I can show live contracts between overlapping records.
I want to be able to count the number of days for each contract that is live between the dates of the parameters.
For example my contract might run from 1st to 30th November but I might want to report from 10th October to 10th November so the number of days I need the query to return is 10 days as my contract is not live before the 1st November. Or my query might run from 1st October to 31st December, then I would need it to show 30 days as it covers the whole of this contract.
