time calculation problem

[reportyemi]

Having read most of the postings and tried many of the suggestions, i cannot
do this. I need help

I want to calculate the difference between times. The times are in hh:mm.pm
or am format (example 02:30pm or 11:14am). I tried to put a calculated time
from two other fields into a new field in my form but all it gives me is
#name? when i use date diff. here was what was in my control
=DateDiff("n",[tblinfantone]![Date_of_Birth],[tblMaternal]![Date_of_Placenta_removal])

For example i have Time_of_Birth as 01:00am and Time_of_placenta_removal as
04:00am. I want it to give me the answer in hours and minutes (3hrs) or if
the time of birth was 02:30pm and time_of_placenta_removal is 02:44pm, i want
the answer to be 14min.

I know its discouraged to have calculated data on a form so i even tried to
create a querry but i got confused doing this. I am using access 2007

Needing help. Spend 3am to 630am trying this and very frustrated

yemi

 

[Al Campagna]

reportyemi,
Given [BT] = BirthTime, [PT] = PlacentaTime.. (you use your own names)
=Int(DateDiff("n",[BT],[PT])/60) & " hrs " & DateDiff("n",[BT],[PT]) Mod 60
& " mins"

BT = 8:30 PT = 3:00 pm
6 hrs 30 mins

This will work in a query, and yes... you shouldn't be saving this value
to your table. Just calculate it
"on the fly", in any subsequent form,query, or report.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[KARL DEWEY]

I tried to put a calculated time from two other fields into a new field in
my form but all it gives me is #name?
The #name means Access does not recognize the field as valid.
Try putting your formula in the query that feeds your form like this --

Separation_Time:
Int(DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal])/60)
& " hrs " &
DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal]) Mod 60 & " mins"

 

[John Spencer]

You still have a problem since you are not recording the DATE along with
the time.

If Date of Birth is 11:42 PM and Placenta Removal is 00:30 AM this
calculation will not work well unless you are also storing the data
along with the time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

my form but all it gives me is #name?
The #name means Access does not recognize the field as valid.
Try putting your formula in the query that feeds your form like this --

Separation_Time:
Int(DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal])/60)
& " hrs " &
DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal]) Mod 60 & " mins"

 

[reportyemi]

friends, I had been busy with other parts of my database. Now i have returned
to this issue. And yes, john spencer was right about the midnight time
issues. I am in the meantime accepting this .

i could not get the above formulas to work. i read this one and tried it and
it worked in a query quite well


Format(DateDiff("n",[tblinfantone]![Time_of_Birth],[tblMaternal]![Time_of_Placenta_removal])/1440,"Short Time")

so the query gave me the time differences for all the patients. How do i get
the answer of each patient on their record?. Is there a way to ensure i get
the time difference of each patient on their form? Just so that i am clear,
there is a time the baby is born and there is a time the placenta comes out.
That time difference is the duration of third stage and i wanted my query to
automatically fill a part of the form with this calculation once i have put
the two times in somewhere else. How do i do this?

Thanks

yemi

You still have a problem since you are not recording the DATE along with
the time.

If Date of Birth is 11:42 PM and Placenta Removal is 00:30 AM this
calculation will not work well unless you are also storing the data
along with the time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I tried to put a calculated time from two other fields into a new field in

my form but all it gives me is #name?
The #name means Access does not recognize the field as valid.
Try putting your formula in the query that feeds your form like this --

Separation_Time:
Int(DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal])/60)
& " hrs " &
DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal]) Mod 60 & " mins"

 

[Al Campagna]

reportyemi,
The time difference between Birth and Placenta would not need
to be saved to a table field.
As an example... Price * Qty = LineTotal. Given that you saved
the Price and the Qty, you only need to display the calculated result in any
subsequent query, form or report. As a general rule... don't save a
calculation
to a table

Place a text control on your form with a ControlSource of...
(all on one line)
=DateDiff("n",[Time_of_Birth],[Time_of_Placenta_removal])/1440

and format the field for Short time. This will always "display" the
difference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

friends, I had been busy with other parts of my database. Now i have
returned
to this issue. And yes, john spencer was right about the midnight time
issues. I am in the meantime accepting this .

i could not get the above formulas to work. i read this one and tried it
and
it worked in a query quite well


Format(DateDiff("n",[tblinfantone]![Time_of_Birth],[tblMaternal]![Time_of_Placenta_removal])/1440,"Short
Time")

so the query gave me the time differences for all the patients. How do i
get
the answer of each patient on their record?. Is there a way to ensure i
get
the time difference of each patient on their form? Just so that i am
clear,
there is a time the baby is born and there is a time the placenta comes
out.
That time difference is the duration of third stage and i wanted my query
to
automatically fill a part of the form with this calculation once i have
put
the two times in somewhere else. How do i do this?

Thanks

yemi

You still have a problem since you are not recording the DATE along with
the time.

If Date of Birth is 11:42 PM and Placenta Removal is 00:30 AM this
calculation will not work well unless you are also storing the data
along with the time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I tried to put a calculated time from two other fields into a new
field in
my form but all it gives me is #name?
The #name means Access does not recognize the field as valid.
Try putting your formula in the query that feeds your form like this --

Separation_Time:
Int(DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal])/60)
& " hrs " &
DateDiff("n",[tblinfantone].[Date_of_Birth],[tblMaternal].[Date_of_Placenta_removal])
Mod 60 & " mins"

 

[John Spencer]

I don't think that works very well. For example

StartTime = #23:59#
EndTime = #00:01#
?DateDiff("n",startTime,endTime)/1440
-0.998611111111111
?Format(DateDiff("n",startTime,endTime)/1440,"hh:nn")
23:58

The difference should be two minutes and not 23 hours and 58 minutes.

I think that this modification to the expression will work to give you
the desired results in minutes. Thanks to Al Campagna for triggering
the thought of using 1440 (number of minutes in a day).

?(DateDiff("n",startTime,endTime)+ 1440) Mod 1440

I haven't done a comprehensive test of the above, but it did seem to
work for the examples I did test. Of course, if someone messes up and
puts in bad data (endTime before StartTime) then you get a bad value
that should look strange (number closer to 1440 than to zero).


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[reportyemi]

Thank you guys. The formula worked in minutes as john mentioned. But Al's
previous formula though it didnot work gave me the format i wanted (hr:min).
So now i want a Al's format and Johns accuracy .

Secondly, i have tried incorporating into the form as AL suggested but the
advice is not working. I put the formula in the control source and changed
format to no avail.

Thanks again
yemi

I don't think that works very well. For example

StartTime = #23:59#
EndTime = #00:01#
?DateDiff("n",startTime,endTime)/1440
-0.998611111111111
?Format(DateDiff("n",startTime,endTime)/1440,"hh:nn")
23:58

The difference should be two minutes and not 23 hours and 58 minutes.

I think that this modification to the expression will work to give you
the desired results in minutes. Thanks to Al Campagna for triggering
the thought of using 1440 (number of minutes in a day).

?(DateDiff("n",startTime,endTime)+ 1440) Mod 1440

I haven't done a comprehensive test of the above, but it did seem to
work for the examples I did test. Of course, if someone messes up and
puts in bad data (endTime before StartTime) then you get a bad value
that should look strange (number closer to 1440 than to zero).


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

reportyemi,
The time difference between Birth and Placenta would not need
to be saved to a table field.
As an example... Price * Qty = LineTotal. Given that you saved
the Price and the Qty, you only need to display the calculated result in any
subsequent query, form or report. As a general rule... don't save a
calculation
to a table

Place a text control on your form with a ControlSource of...
(all on one line)
=DateDiff("n",[Time_of_Birth],[Time_of_Placenta_removal])/1440

and format the field for Short time. This will always "display" the
difference.

 

[John W. Vinson]

Thank you guys. The formula worked in minutes as john mentioned. But Al's
previous formula though it didnot work gave me the format i wanted (hr:min).
So now i want a Al's format and Johns accuracy .

=Format(((DateDiff("n",startTime,endTime)+ 1440) Mod 1440)/1440, "hh:nn")

 

[reportyemi]

Worked perfectly . Thank you. Now how do i get it into the form??

Thanks

yemi

 

[John W. Vinson]

Worked perfectly . Thank you. Now how do i get it into the form??

By setting the Control Source of a textbox on the form to

=Format(((DateDiff("n",startTime,endTime)+ 1440) Mod 1440)/1440, "hh:nn")

replacing startTime and endTime with the names of controls on the form
containing those values; or by using the same expression as a calculated field
in a Query and including that field in the form's recordsource.

 

[Philip Herlihy]

=Format(((DateDiff("n",startTime,endTime)+ 1440) Mod 1440)/1440, "hh:nn")

The OP has confirmed that he is storing dates and times in separate
table fields, as editing a "General" date format is messy, and he's keen
to keep things simple for the users. In any event I belive the (neat!)
expression above would return an incorrect result on those rare
occasions where the placenta lingers more than 24 hours after the birth.

I've puzzled over similar problems recently, as I've recently migrated
familiar applications from Access 2003 to 2007. In the former I was
using the DTPicker control (**), which seemed to allow me to use two
controls on the same underlying field, and somehow managed to avoid
problems when I edited either: both were correctly updated. I've tried
this using text boxes in A2007 (which conveniently provide a calendar
popup when the control is formatted as a date), but (particularly when a
record is being entered but has yet to be saved) I've had problems when
revising the field contents via a control formatted as a time: the
sibling control (formatted as a date) loses the integral part of its
value (perhaps understandably).

In my own application I'm simply choosing to edit only the "date"
control, which appears as a "general" date/time format when clicked.
That wouldn't do for applications for others to use, I think, unless I
make the "time" control read-only. I've wondered about using an event
handler to intercept the attempt to edit in order to present a popup
form with separate date and time text boxes which are then combined
correctly (added!) when this popup form is closed. Alternatively, there
may be a successor to the elderly DTPicker control which would save me
the trouble. Or am I barking up the wrong tree?

(**) http://msdn.microsoft.com/en-us/library/aa231249(VS.60).aspx

Phil, London

 

[John Spencer]

If you have the date and time you can combine them and get the
difference in minutes and then format that into an hours and minutes string.

DateDiff("n",DateStart + TimeStart,DateEnd + TimeEnd)

Where X is the above expression
X\60 & ":" & Format(X Mod 60,"00")

Replace X with the expression.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Philip Herlihy]

If you have the date and time you can combine them and get the
difference in minutes and then format that into an hours and minutes
string.

DateDiff("n",DateStart + TimeStart,DateEnd + TimeEnd)

Where X is the above expression
X\60 & ":" & Format(X Mod 60,"00")

Replace X with the expression.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

You think it's better to store the dates and times separately, with
separate controls?

Phil

 

[John Spencer]

No, I don't think it is better in most situations. It works better, in most
situations, to have the date and time in one field and use functions such as
DateValue and TimeValue to get separate date or time. Or Format to display a
specific part of the dateTime value.

That said, there are times and reasons for using two fields. For instance if
you need to retrieve data between two specific times over a large number of
records having the time in a separate indexed field could provide a
significant performance boost.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Philip Herlihy]

You think it's better to store the dates and times separately, with
separate controls?

Phil

I've been experimenting with this.

What I wanted was to avoid users having to edit a date/time in "General
Date" format, eg: "05/06/2009 20:06:00". I'd also prefer not to have
separate Date/Time fields, as (eg) the Date value could also contain
unused Time information and vice versa - which seems all wrong!

I figured I could have two "views" (via differently formatted controls)
on the same underlying Date value, so that users could edit Date and
Time independently. The trouble is, if you put (only) a time in the
"Time" view, you discard the Date component, although the built-in Date
picker doesn't seem to discard Time information.

I thought I'd be able to work something out by using event handlers on
the controls. This is where I got to before giving up (for now):

There are two controls: DateofAdmission_txt and TimeofAdmission_txt,
both bound to the same Date field in the Record Source. Here are the
two event-handlers (error code simplified):

===================================================================
Private Sub TimeofAdmission_txt_GotFocus()
Dim TimePart As Date
On Error GoTo bust:
'save (global) any existing date/time
tempDate_g = Nz(Me!DateofAdmission_txt, 0)
Me!TimeofAdmission_txt.BackColor = vbRed ' visual candy
'pick out fractional part
TimePart = Me!TimeofAdmission_txt - Int(Me!TimeofAdmission_txt)
' hoped next would stop edit string appearing as General Date!
Me!TimeofAdmission_txt.Format = "Short Time"
Me!TimeofAdmission_txt = TimePart
Exit Sub
bust:
MsgBox "whoops"
End Sub
===================================================================

===================================================================
Private Sub TimeofAdmission_txt_LostFocus()
Dim DatePart As Date
Dim TimePart As Date
On Error GoTo bust:
'pick out integer part of locally stored date value
DatePart = CDate(Int(tempDate_g))
'pick out fractional part
TimePart = Me!TimeofAdmission_txt - Int(Me!TimeofAdmission_txt)
'combine to make Date/Time
Me!DateofAdmission_txt = CDate(DatePart + TimePart)
Me!TimeofAdmission_txt.BackColor = vbWhite 'candy
Exit Sub
bust:
MsgBox "whoops"
End Sub
===================================================================

It _almost_ works, but I've found that if I move to the next record
while the TimeofAdmission_txt control has focus the updates are made to
the wrong record! (Nasty!) Just shows what a minefield it can be to
use event handlers to mess with data.

I've wondered if a workable solution might instead be obtained by making
the two Date and Time fields Unbound, populating these two controls from
an On Current event handler ready for editing, and combining the edited
values from a Before Update event handler.

Comments very welcome.

Phil

 

[Philip Herlihy]

I've been experimenting with this.

What I wanted was to avoid users having to edit a date/time in "General
Date" format, eg: "05/06/2009 20:06:00". I'd also prefer not to have
separate Date/Time fields, as (eg) the Date value could also contain
unused Time information and vice versa - which seems all wrong!

I figured I could have two "views" (via differently formatted controls)
on the same underlying Date value, so that users could edit Date and
Time independently. The trouble is, if you put (only) a time in the
"Time" view, you discard the Date component, although the built-in Date
picker doesn't seem to discard Time information.

I thought I'd be able to work something out by using event handlers on
the controls. This is where I got to before giving up (for now):

There are two controls: DateofAdmission_txt and TimeofAdmission_txt,
both bound to the same Date field in the Record Source. Here are the
two event-handlers (error code simplified):

===================================================================
Private Sub TimeofAdmission_txt_GotFocus()
Dim TimePart As Date
On Error GoTo bust:
'save (global) any existing date/time
tempDate_g = Nz(Me!DateofAdmission_txt, 0)
Me!TimeofAdmission_txt.BackColor = vbRed ' visual candy
'pick out fractional part
TimePart = Me!TimeofAdmission_txt - Int(Me!TimeofAdmission_txt)
' hoped next would stop edit string appearing as General Date!
Me!TimeofAdmission_txt.Format = "Short Time"
Me!TimeofAdmission_txt = TimePart
Exit Sub
bust:
MsgBox "whoops"
End Sub
===================================================================

===================================================================
Private Sub TimeofAdmission_txt_LostFocus()
Dim DatePart As Date
Dim TimePart As Date
On Error GoTo bust:
'pick out integer part of locally stored date value
DatePart = CDate(Int(tempDate_g))
'pick out fractional part
TimePart = Me!TimeofAdmission_txt - Int(Me!TimeofAdmission_txt)
'combine to make Date/Time
Me!DateofAdmission_txt = CDate(DatePart + TimePart)
Me!TimeofAdmission_txt.BackColor = vbWhite 'candy
Exit Sub
bust:
MsgBox "whoops"
End Sub
===================================================================

It _almost_ works, but I've found that if I move to the next record
while the TimeofAdmission_txt control has focus the updates are made to
the wrong record! (Nasty!) Just shows what a minefield it can be to
use event handlers to mess with data.

I've wondered if a workable solution might instead be obtained by making
the two Date and Time fields Unbound, populating these two controls from
an On Current event handler ready for editing, and combining the edited
values from a Before Update event handler.

Comments very welcome.

Phil

Just a correction: I've noticed that the form in which I've been playing
with this is displaying records more than once, which means that the
update was certainly showing in the next record in the recordset, but it
was somehow the same record. I'm rather slow to figure out SQL, so I'm
puzzled by this. The record source is:

SELECT tblMaternal.*, tblinfantone.*
FROM tblMaternal INNER JOIN tblinfantone ON tblMaternal.Maternal_ID =
tblinfantone.Maternal_ID;

... which isn't an idiom I'd have used (I'm using a borrowed database).

So maybe my "event handler" solution was working after all, but I'd need
to do a lot more testing (and figure out the implications of that Record
Source) before I could use this approach.

Simplicity seems an attractive quality, this evening...

Phil

 

[Philip Herlihy]

Just a correction: I've noticed that the form in which I've been playing
with this is displaying records more than once, which means that the
update was certainly showing in the next record in the recordset, but it
was somehow the same record. I'm rather slow to figure out SQL, so I'm
puzzled by this. The record source is:

SELECT tblMaternal.*, tblinfantone.*
FROM tblMaternal INNER JOIN tblinfantone ON tblMaternal.Maternal_ID =
tblinfantone.Maternal_ID;

.. which isn't an idiom I'd have used (I'm using a borrowed database).

So maybe my "event handler" solution was working after all, but I'd need
to do a lot more testing (and figure out the implications of that Record
Source) before I could use this approach.

Simplicity seems an attractive quality, this evening...

Phil

That was just a "senior moment". I was working under time pressure on a
small part of a larger application, and I'd failed to register that the
originator had changed the architecture slightly. I'd got it into my
head that I was seeing a "cross product", which of course I wasn't...

Phil

 

[Philip Herlihy]

I've been experimenting with this.

What I wanted was to avoid users having to edit a date/time in "General
Date" format, eg: "05/06/2009 20:06:00". I'd also prefer not to have
separate Date/Time fields, as (eg) the Date value could also contain
unused Time information and vice versa - which seems all wrong!

I figured I could have two "views" (via differently formatted controls)
on the same underlying Date value, so that users could edit Date and
Time independently. The trouble is, if you put (only) a time in the
"Time" view, you discard the Date component, although the built-in Date
picker doesn't seem to discard Time information.

I thought I'd be able to work something out by using event handlers on
the controls. This is where I got to before giving up (for now):

There are two controls: DateofAdmission_txt and TimeofAdmission_txt,
both bound to the same Date field in the Record Source. Here are the
two event-handlers (error code simplified):

===================================================================
Private Sub TimeofAdmission_txt_GotFocus()
Dim TimePart As Date
On Error GoTo bust:
'save (global) any existing date/time
tempDate_g = Nz(Me!DateofAdmission_txt, 0)
Me!TimeofAdmission_txt.BackColor = vbRed ' visual candy
'pick out fractional part
TimePart = Me!TimeofAdmission_txt - Int(Me!TimeofAdmission_txt)
' hoped next would stop edit string appearing as General Date!
Me!TimeofAdmission_txt.Format = "Short Time"
Me!TimeofAdmission_txt = TimePart
Exit Sub
bust:
MsgBox "whoops"
End Sub
===================================================================

===================================================================
Private Sub TimeofAdmission_txt_LostFocus()
Dim DatePart As Date
Dim TimePart As Date
On Error GoTo bust:
'pick out integer part of locally stored date value
DatePart = CDate(Int(tempDate_g))
'pick out fractional part
TimePart = Me!TimeofAdmission_txt - Int(Me!TimeofAdmission_txt)
'combine to make Date/Time
Me!DateofAdmission_txt = CDate(DatePart + TimePart)
Me!TimeofAdmission_txt.BackColor = vbWhite 'candy
Exit Sub
bust:
MsgBox "whoops"
End Sub
===================================================================

It _almost_ works, but I've found that if I move to the next record
while the TimeofAdmission_txt control has focus the updates are made to
the wrong record! (Nasty!) Just shows what a minefield it can be to
use event handlers to mess with data.

I've wondered if a workable solution might instead be obtained by making
the two Date and Time fields Unbound, populating these two controls from
an On Current event handler ready for editing, and combining the edited
values from a Before Update event handler.

Comments very welcome.

Phil

Clarification: updates were _not_ being applied to the wrong record; the
same record was contributing to a one-to-many join, and the update was
correctly being preserved when the same (Mother) appeared against
another infant. Some of us need to resist the temptation to work late...

I now rather think that with (much more) thorough testing this approach
might work. It also seems that the approach using "unbound controls"
loaded from On-Current for editing should work.

What I did after giving up (prematurely?) on the first approach was to
go back to using two seperate table fields (meaning four fields are
involved in the calculation of an interval) and created a public
function which derives the correct interval from those fields supplied
as arguments, and a call to that function is the control source of the
(three) "interval" textboxes.

Of the three, I think I rather prefer the "unbound controls"
/On-Current/Before-Update approach. Comments welcome, as ever.

Phil