Time difference between two records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work in a blood draw room and we want to keep our patient log on Access.
The log must have a time field for when the patient is brought into the room.

Is it possible to calculate the difference between times? For example:

Patient A was seen at 9:00
Patient B was seen at 9:03

Can the difference between 9:00 and 9:03 be calculated either in the table
or a report?
 
sure, you can use the DateDiff function to do this. It will return the
number of minutes, or hours or days between 2 times/dates. You would want to
do this in a query, on a form and/or in a report. As a best practice you
usually don't want to put calculated values like this in your tables, just
store the raw data and do the calculation wherever you want to display the
results.
 
Thanks Dean. However the problem I have is that I don't really have a "start
time" and "end time." Each patient is entered into the database and the time
is stamped on the record. Then a new patient is put in and stamped on that
record.

My table looks like this:

ID Date SSN LastName FirstName Time
01 12/7/2005 111-11-1111 Smith John 09:00
02 12/7/2005 222-22-2222 Johnson Michael 09:03

Its the difference between these two records that I want to calculate the
time.

Can that be done with DateDiff?
 
One possibility assuming that the ID is a sequential number with no gaps:

SELECT tblVisit.*, tblVisit_1.VisitTime AS NextTime
FROM tblVisit
LEFT OUTER JOIN tblVisit AS tblVisit_1
ON tblVisit.ID + 1 = tblVisit_1.Id

Then just subtract the original visit time from NextTime.

Another possibility involves a report and using:
(1) A recordsource of SELECT * FROM tblVisit ORDER BY Date DESCENDING,
Time DESCENDING
(2) a text box with a control source of = OldValue
(3) a text box with a control source of = TimeDifference

and the following code:

Dim intOldvalue As Integer 'I don't know what the datatime of your time
field is so I made it integer
Dim intPreviousValue As Integer ' same comment

Private Function OldValue() As Integer
OldValue = intPreviousValue
intPreviousValue = intOldvalue
End Function

Private Function TimeDifference() As Integer
TimeDifference = Me.TimeField - intOldvalue
intOldvalue = Me.TimeField
End Function

You will have to change the data types appropriately.
Also, it's not a good idea to have variables or field names called "Date" or
"Time"
 
Back
Top