Queries :: How To Calculate Number Of Weeks Between Two Dates
Jun 25, 2013
My database includes two separate dates related to a vehicle purchase and default payments resulting in repossession. I need to find out how many weeks passed between those dates.
Example:
03/14/12 Date of automobile purchase
06/06/13 Date of last payment before default
How many weeks in between? And then I need to average all of those figures so my company can see the usual week-term time frame that our customers are defaulting and getting repossessed.
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 have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
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?
Basically, I am trying to calculate a integer number difference from two dates (TAT = Due-Date - Result_Date). The number is calculated and excludes weekends and ideally holidays (for that I have a tblHoliday but not sure how to use it). The function below seems to calculate a number but doesn't exclude weekends.
For example, if Due_Date is 9/26/2014 and Result_Date is 9/30/2014, then TAT is calculated to be 5 (should be 2).Since 9/26/2014 is a Friday only Friday and Monday are used in the calculation.
Code:
Option Compare Database Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long Dim intCount As Long intCount = 0
Hello, I am new to Access and I am having a problem with something. I have a query which returns, among other things, dates. I need to organize (group) the dates into weeks. Does Access have a function which could do this? Or some other way of grouping the dates? Or How would I got about writing a function in Access? This is done in a Pivot Table right now, and I just use a calculated field which is the date / 7. This gives some god awful numbers (like 5944), and not anything like what I want. I would want the function to return something like, "Feb. 5, 2005 - Feb. 12, 2005". Any suggestions?
My main query has a list of events with the corresponding dates that they occured. I want to create a second query that shows Monday’s date of the current week and the Monday date of the preceding 51 weeks.
How do I get my second query to show the dates for 52 consecutive Mondays starting with the current week (Monday date) and going back 51 weeks.
Then I want to create a third query by combining the first two such that the with the dates and events main query are displayed and grouped by the Monday dates for the year query.
I'm trying to build a query with an iif statement to calculate expiration dates within a query. For some reason what i'm trying to do is not working as expected.
Here's the situation. Every employees with an exception of two employees in the company has an expiration 2 years after the class took place. The two exceptions are the certified instructors who's training certification is good for three years.
I've created a query with the employeeid, training subject, and maxoftraining date.
I'm trying to write an if statement to give me the expiration date based on the above information. Here's what I have so far; however its not calculating based on the criteria.
Expiration: IIf([tblemployee]![EmpID]=1 Or 2,DateAdd("yyyy",3,[maxoftrainingdate]),DateAdd("yyyy",2,[maxoftrainingdate]))
At this point in time, all "expiration" dates are showing + 3 years rather than just the Employee ID's 1 and 2 and the rest +2 years.
I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.
I receive a report daily. From this report I need to calculate the number of successful backups within a window, and number of successful backups outside a window. The window is 7:00 a.m. The column contains date and time (10/15/2014 7:00 AM). Right now I have written the following query =Date() And > #7:00:00 AM#
I do not get any results even though there are 79 records that fit the criteria.
In the attached image I was trying to calculate number of working days(throughput) b/w item received_date and item_released date by using datadiff with "w" interval . but if I use "w" interval it brings value zero .but if I use "d" interval then it brings result with inclusive of Saturdays and Sundays.i don't need that.
How to calculate the number of working days exclusive of Saturdays and sundays.
How to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria?
I want to count number of mountpoints appear in the table.
As below sample table, - Different mountpoints will appear, based on number of server using it. As you can see, the mountpoints will have repeating name based on number of server using it. - The Count_Mountpoints will calculate number of total Mountpoints appear in the list.
I am calculating number of weeks between start and finish date. Some of the users have questioned the validity of calculation as they are not too sure if this is being calculated as Monday to Friday week or does it work by calculating 7 days. If the latter is true, then I should see a decimal value. I am using date diff formula.
I am trying to count how many of the "same" and "differences", as well as calculate the percentages of the number of "same" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.
Also, I need to categorize these diagnosis by each type of diagnosis.
I just can't seem to get this one to work right. I've got the following query. I need to count the number of Null dates or show zero if there are no Null Dates.
Code: SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, Count(qryNoticeResponseNew.[fldResponseSeen]) AS fldCount FROM qryNoticeResponseNew GROUP BY qryNoticeResponseNew.fldNoticeID;
Which is just counting the number of dates so far. It got me to thinking I need to do something like this.
Code: SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0) AS fldCount FROM qryNoticeResponseNew GROUP BY qryNoticeResponseNew.fldNoticeID;
Which pops a "cannot have aggregate function in expression" error.
I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber
I also have a table called FCDateRange with three fields
DateStart DateEnd and DotNumber
For example
3/7/13 - 3/13/13 - 1 3/14/13 - 3/20/13 - 2
Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field
I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
I'm writing a simple job scheduling database, so I've got resource availability (by week) and job list (by week). I've written a single-column query whose only output is a unique list of weeks with resources available, and another single-column query whose only output is a unique list of weeks with at least one job allocated.
How do I join these so that I can get a single-column list of weeks that have either (or both) of resources and jobs?
So Query1 returns: 25/08/2014 08/09/2014 15/09/2014
while Query2 returns: 01/09/2014 08/09/2014 15/09/2014 22/09/2014
I want query3 to return: 25/08/2014 01/09/2014 08/09/2014 15/09/2014 22/09/2014
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate 1 John Doe 05/06/2014 06/27/2014 2 Jane Doe 04/24/2014 05/15/2014 3 Steven Smith 05/15/2014 NULL/Empty 4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden) John Doe 5/19/14-5/25/14 Checkbox Null 1 John Doe 5/26/14-6/1/14 Checkbox Null 1 John Doe 6/2/14-6/8/14 Checkbox Null 1 John Doe 6/9/14-6/15/14 Checkbox Null 1 John Doe 6/16/14-6/22/14 Checkbox Null 1 John Doe 6/23/14-6/29/14 Checkbox Null 1 Jane Doe 4/28/14-5/4/14 Checkbox Null 2 and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
