Queries :: Number Of Days Between Two Dates With Irregular Days Worked
Apr 14, 2014
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
My subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
I have a table of employees, and dates they worked on. These are seasonal employees who want to get in as many days as possible before the season ends, but regulation states they must take a compulsory rest day after x days.So I need to create a query that can return the list of employees, with a count of consecutive workdays up until current date.
If today is 20/09/2013, and Johnny worked on, 19, 18, 17, 15, 14, 13, his count must be 3, because he was absent on 16. Therefore only from 17 through 19 is regarded as consecutive.
If Peter worked 19, 18, 17, 16, 15, 14, 13, his count would be 7, because unlike Johnny, Peter still worked on 16.
I'm trying to calculate the number of days between two dates using the iif statement
Fields: [LDW] "Last Day Worked" [ReturnedDate]
DESCRIPTION: If ReturnedDate is null, then calculate the datediff "d" between LDW with Now(), if not, then calculate the datediff "d" between LDW with ReturnDate.
I tried this but it didn't work at all.
Days Absent: Iif(isnull([ReturnedDate],(DateDiff("d",[LDW],Now())),(DateDiff("d",[LDW],[ReturnedDate])))
Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.
I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.
The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.
This is a formula I tried to piece together but obviously not correct as it's not returning anything;
IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure], Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))
I need to work out a couple of calculations, the number of days between an engineer starting and finishing a project, the number of days between the project being put on the database in the first place and being completed by an engineer and finally, the difference between these. These maybe simple, but I have no idea tbh (still new to access).
I have a form with the date it was added onto the database, and the engineer fills in the date started and date finished as it happens.
These run off of 2 tables BookingIntbl and TechnicalServicestbl
[BookingIntbl].[Date], [TechnicalServicestbl].[Date Started] and [TechnicalServicestbl].[Date Finished]
I have then got the fields, 'Days with Engineer', 'Days in Repair Centre' and 'Day Difference' for the answers to be displayed.
I want the number of days between a specific date (roll out date) and 12/31/2005 so that I can use the number to project revenue for this year.
This is what I'm using but it doesn't give me the right number. I don't know how to transform something to a datetime variable so maybe that's the reason. Can you help me out?
Expr4: Day(12/31/2005-[Scheduled Roll Out Date]-0.49999)
I am making a basic hospital management system in Access 2013.I have two tables named "Bed" and "Receipt". Bed(BedID,AssignedDate,PatientID,DischargeDate,Bed Charges) Reciept(ReceiptID,PatientID,BedCharges) I want to calculate "BedCharges" by calculating the number of days using "AssignedDate" and "DischargeDate" and then multiplying with a constant amount of charges per day. Also the BedCharges calculated in "Bed" Table also needs to be in the "Receipt" table. How can I count the number of days and then calculate the "BedCharges" in both the tables?
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?
My database keeps track of training for employees, some of which expires after a period of time, which is in the tables. I need to create a query that displays only the employees and the training that expires during the next 30 days. I have a query that displays the expiration date (this field uses DateAdd and adds the expiration term to the date the employee completed the training) I am trying to add a criteria to this field. I found <=Date()+30 in my searches but that displays all of the records from 12/14 and back. I have tried all kinds of versions of this (obviously, not the correct one) and either get all records or none. (Yes there are records in the database that fall within the the period I am trying to display - that is something that I have been known to forget)
Is it because I am using criteria in a field that is based on DateAdd or what would be the correct criteria for this?
I have 2 dates that I need to count between. Easy enough just use the datediff right? Nope cause it won't count just the 5 workdays. I researched and found that the "w" in the function doesn't work the way I need it to. I found lots of code to make a module that will do it for me and they all include having a holiday table. Right now I don't need a holiday table I just want the simple dates in between.
I have a query that records a status update, and the date it was made by an employee etc. The table has a secondary key related to ... lets say a project table.
E.g. Status Id; EmployeeID;ProjectsID;Status;Date
I want to calculate the average amount of days it takes for a status change occur.
I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:
tblRegion = RegionID tblStatus = StatusDate
I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.
I am building a report and need to know the coding in the query (or in the report) to achieve the following.
Currently, in my query - 3 fields that work fine:
AppDate - dates of set appointments (Appt)
MonthNo: DatePart("m",[App_Date]) - get Month value
YearNo: DatePart("yyyy",[App_Date]) - get Year value
What I need is : EXACT coding to get these counts to show in my report:
January 2013 12 appointment (there were a count of 12 appts in January) over 23 days (there were 23 days where appts were booked in January) of 31 days (total number of days in the month of January)
February 2013 18 appts (there were a count of 12 appts in February ) over 19 days (there were 23 days where appts were booked in January) of 28 days (total number of days in the month of February )
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 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.
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 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.
