I currently have a table that has patient ID (unique record (SSN)) and a date of visit field. The SSN may have numerous rows with different dates of visits. What I am trying to do is a DateDiff function that will retrieve records that are 48 hours apart or 2 days apart. We are tracking patients that return within 48 hours of a previous visit.
View RepliesI have a list of client activity - client name, loan ID and loan date. I would like to create a new field that shows the number of days between one loan and the next. If I was doing it in excel, I would need to sort the data by client name and loan date and then calculate the number of days between the loan date of one loan and the loan date of the loan immediately prior to this.
View 14 Replies View RelatedIn access in one field I have one date and in another field I have another date. I want to calculate the number of years.,months and days elapsed between these two date. e.g. suppose in one field I have 30.06.2005 and in other 20.04.2004 then it should show 1 year 2 months and 10 days.
View 14 Replies View RelatedHi, I am a longtime Excel user, and am now migrating some work to Access.
I have 2 fields dealing with dates in the format mm/dd/yyyy hh:mm, OPEN and CLOSE
I'd like to get the difference between the two in hours, but I have no idea how or where to do this in Access. I'm assuming it is put into the queries, but it would be easier if I could just put it in the table.
Here is an example
1/1/2008 1:00 1/2/2008 2:00
I want the result to be "25"
Thanks in advance!
I'm trying to create a database for a Cattery business.
One thing I'd like to include is a query that calculates how long a cat has been staying at the cattery and then calculating the amount owed by the owner.
I don't have a cost field in any of my tables, so I need to create this on every run of the query.
So for example
A Cat resides in the cattery from the 22nd of Feb to the 28th of Feb. So 6 days (not including the day the cat is brought into the cattery) at a cost of 4.95 a day equals 29.7
Heres what I have so far
http://www.tdh1987.co.uk/pics/query.JPG
Bear in mind I'm from the UK, so the date field is in a different format to yours.
Any help will be greatly appreciated.
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
I need help to calculate time difference from the record above where ID is same. I have attached an example in excel with this request where I have if statement doing exacly what I want to do in Access. I have a table in Access that has three columns - as below
ID, Reg, TimeDiff
66646, 14/06/2007 21:33,
66646, 15/06/2007 06:03, 8:30:00
66646, 15/06/2007 12:20, 6:17:00
66646, 15/06/2007 15:08, 2:43:00
67844, 14/06/2007 04:39,
67844, 14/06/2007 09:05, 4:26:00
67844, 14/06/2007 09:08, 0:03:00
67844, 14/06/2007 16:27, 4:09:00
If ID is equal to the ID in record above then TimeDiff is equal to reg minus reg in the above recored, else blank.
Hope someone can help me with this.
Thanks, GS
I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..basically the structure is
equipment number call number bas start date
12345678 112255 1/7/14
12345678 112256 3/7/14
What i'm after is the 4th column to work out the date diff... in this case 2 days the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..
I have created a database in Access where parents of students may come in and volunteer and receive free time credit from their employer (those companies who participate).
The database runs fine: Main table (demographics; student table; signIn/signOut table; and Volunteer Duty (a combo box that list all of the approved volunteer duties to qualify for the time credit) Every thing works perfect with the exception of my signIn/signOut form. After struggling with this for countless hours, I have settled for a manual entry of the signIn and signOut time, entered at the time of their start/stop times. I would like for it to be an automated function, but I have had such a difficult time up to this point I will settle for this method. What I NEED is to figure out how to make my "total vol hours today" field calculate the total volunteer hours at the time the parent "signOut".
If some of you Access MVP's can help with this I will be very grateful.
I awaite your answers, and THANKS IN ADVANCE.
signed: "head spinning like-a-top"
I'm trying to make a query where it can calculate the difference between a table field value and a query field value.
Ex. Table INVOICE - fields
invoice no, invoice value,
Query TOTAL - fields
invoice no total invoice value
I need to get the difference between invoice value and total invoice value.
How can I put this into a query (invoice value - total invoice value)?
I have a query that must calculate the different between values in two different records, based on the date.
The underlying ORIGVALUES table contains:
Date; Value; Diff
1/10/15; 100; (this is what I am trying to calc)
1/11/15; 101; (this is what I am trying to calc)
1/14/15; 102; (this is what I am trying to calc)
What is the best way to have each record calculate the selected date value divided by the prior date value)? E.g.
For 1/11 the math is 101 / 100 (so the 1/11/15 record would show the result of 1.01)
For 1/14 the math is 102 / 101 (1/11 to 1/14 represents a weekend)
I could copy the prior day values to the current day records and do that math easily but that is very redundant.
i have a table that has meter readings for copiers on it and dates.
i need to add another column to show the differences in the meter field between itself and the row entered before it (the month before) to find out how many copies were made. i can do this in excel very easily, but cant figure out how i would do it in access
sample:
Date Meter Diff.
06/01/2006 0 0
07/01/2006 1000 1000
08/01/2006 1500 500
we will be entering the date and the meters in every month, but i need to have it calculate the difference between months so i can then calculate overage, if any.
I have a query that is looking to calculate staff absence.
Absence Start date & Return to work date
Looking to calculate number of days (not a problem) but to exclude weekends ie saturday and sunday ????
Not sure if this can be done --- any assistance would be greatly received.
thanks and regards
ian watson
Yarm Cleveland UK
I am starting to create a resourcing database that needs to be able to work with dates and times and perform calculations on them.
I need to work out the number of hours. I have two date/time fields Day_Start_Time and Day_End_Time. I want to calculate the difference between the two in hours and then multiply by the number of working days.
I tried this: Number_Of_Hours: DateDiff("hh",[Day_Start_Time],[Day_End_Time])*[Number_of_days]
But get #Func! when I run the query.
I have the below query (A). How do I calculate the Day of Therapy as shown in B
A
MRN Last Name DOA Service startDate INR Dose WarfID VisitID
12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138
12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138
12001 Smith 6/28/2013 Warfarin 7/1/2013 1.3 6 mg 12 138
12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138
12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138
12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138
12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138
11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139
11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139
11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139
11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139
11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139
1205972 Jones 6/28/2013 Warfarin 7/6/2013 1.0 6.5 mg 29 138
[code]....
What I really want is to calculate the time (# day) it takes to reach a target goal INR (2-3) I have tried DateDiff between DOA (date of admission) and Start date but this obvious won’t work because some patient won’t start on warfarin until later.
Another possibility is to calculate date difference between two rows in same column but I don’t know how For example Pt 1 = It take 7 days to reach an INR of 2, Patient #2 = it takes 5 days to reach an INR 2.2 and Patient #3 takes 6 days to reach an INR of 2.1. Average = 6 days.
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
I have a query question . How can I extract a CHANGED value" in a column? Example here under table1. Time and TAG makes a record unique, No ID available ...
Table1
time tag value
10:02 Tag1 10
10:02 Tag2 11
10:03 Tag1 10
10:03 Tag2 11
10:04 Tag1 10
10:04 Tag2 12
10:05 Tag1 10
10:05 Tag2 12
10:06 Tag1 10
10:06 Tag2 12
10:07 Tag1 11
10:07 Tag2 12
10:08 Tag1 11
10:08 Tag2 11
I want to have as output
10:04 Tag2 12
10:07 Tag1 11
10:08 Tag2 11
(so at 10:04 tag 2 changed in value and on 10:07 tag1 changed value and 10:08 changed Tag2)
Preferably with the previous value as well, but this is not the highest prio
10:04 Tag2 12 11
10:07 Tag1 11 10
10:08 Tag2 11 12
I have a Transactoin Table that holds the following fields:
TransID (*Primary Key Numerical Value)
OrderID (Non Unique Number Value, One Order can have multiple Transactions attributed to it.
Status (Numerical Value ranging from 0 to 32, each is a different phase in the life of a transaction such as 0 means Transation Created, 1 through 31 are various actions with 32 beign the transaction closing)
Date (The date in which a particular phase of a particular transaction happened.)
Reason (The Description of a phase. This is the only text field whose contents are relative to the Status field in the same table.)
I need to find the amount of time passed from the first Transaction to the last transaction of each individual order, which will be displayed in a report. Unfortunately not every order goes through all 32 phases, in fact most dont. The individual orders have a random amount of phases that they go through but each have a starting one (which ranges from 0 onwards) and an ending one (once again it is not necessarily 32). The dates correspond to the numbers in an individual order which means that if an order has the following phases:
1
2
4
6
Then the dates of each phase will be one after the other like the Status Number as shown above. Anybody knows how to do this? I need to find how much time passed from the first transaction to the last for each individual order qand display it in a report.
I have a table, and in the table I have an assesment date and date of receipt. I would like to have a field in this table which shows the difference between these 2 dates (in days). If this is possible?
Can anyone help me on this matter, I am pulling my hair out. Its most probably very simple for you mega experts.
Hi.
I have one table with dates (1) and object properties. Another table has dates (2) and other info. What I need is a query that selects the dates (1) closest to date (2) and lists the respective properties.
I figured all I needed to do is a WHERE MIN (dates(2)-dates(1)) query. The problem is that Access (2000) rejects this query b/c aggregate functions are not allowed in WHERE statements.
Does anyone have an idea of how to work around this? The structure of the data is such that there will only be one minimum date > 0.
Looking forward to your ideas. THANKS !!
Hello to everybody.
I have a problem in calculating the difference between two dates and displaying the output into Years Months Weeks Days. My problem is that dividing the number of days by 30 and requesting an integer is not accurate enough. I have looked at Pat and Jons examples shown in these threads
http://www.access-programmers.co.uk/forums/showthread.php?t=67472
http://www.access-programmers.co.uk/forums/showthread.php?t=89051&highlight=calculating+number+months
which either method would suit me as the accurate answer is what I am after.
My problem is that not all of my records would have a start and end date. Therefore both examples would result in an error.
Can anyone please give a hint on how to perform the calculations on those fields which have dates recording the results but ignoring those fileds which do not have a start / end date.
I am having a bad day and I'm sure the answer is so easy that I am going to kick myself but having played around with the modules by limited knowledge is not enough.
Many thanks in advance
Ian
I have the attached database which calculate the Difference between dates.I have also two problems like i shown in the image
Code:
Option Compare Database
Option Explicit
Sub Recalculate()
Dim rst As DAO.Recordset
Dim dff As Long
[code]...
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
[code]...
How can I calculate the difference between two dates but I only want to count the work days? So if was today and I wanted to go until 6/15/2015 the difference would be 5 and not 7 because I do not want to count Saturday or Sunday. Is there a special %datediff function where I would only count work days?
View 4 Replies View RelatedI 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.
View 3 Replies View Related