time issues

[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

 

[kc-mass]

Assuming you have a table (can also be form controls) with date/time fields
of
"start" and "end", try something like this:

Sub timecheck()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTime")
rs.MoveFirst
Debug.Print Format(rs![Start] - 1 - rs![End], "Short Time")
End Sub

Times of 11:23 AM and 1:30 PM will yield "02:07"

Regards

Kevin

 

[reportyemi]

It puts 0:00 in te field space and then 1.47 comes up when i place the mouse
over it. The answer should be 3 hrs
The start and end times are in two different tables but one form

Private Sub timecheck()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblinfantone")
rs.MoveFirst
Debug.Print Format(rs![ tblinfantone]![Date_OF_Birth] - 1 - rs![
tblMaternal]![Date_of_Placenta_removal], "Short Time")
End Sub

Assuming you have a table (can also be form controls) with date/time fields
of
"start" and "end", try something like this:

Sub timecheck()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTime")
rs.MoveFirst
Debug.Print Format(rs![Start] - 1 - rs![End], "Short Time")
End Sub

Times of 11:23 AM and 1:30 PM will yield "02:07"

Regards

Kevin

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

 

[reportyemi]

It puts 0:00 in the field space and then 1.47 comes up when i place the mouse
over it. The answer should be 3 hrs
The start and end times are in two different tables but one form

Private Sub timecheck()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblinfantone")
rs.MoveFirst
Debug.Print Format(rs![ tblinfantone]![Date_OF_Birth] - 1 - rs![
tblMaternal]![Date_of_Placenta_removal], "Short Time")
End Sub

Assuming you have a table (can also be form controls) with date/time fields
of
"start" and "end", try something like this:

Sub timecheck()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTime")
rs.MoveFirst
Debug.Print Format(rs![Start] - 1 - rs![End], "Short Time")
End Sub

Times of 11:23 AM and 1:30 PM will yield "02:07"

Regards

Kevin

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

 

[John W. Vinson]

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.

Regardless of format, date/time fields are stored as a number, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such,
it's best to store the date and time together in one field - is your
[Date_Of_Birth] field in fact stored as #1:00am#, or as #8/2/2009 1:00am#? If
you don't have the date, then time ranges spanning midnight will give
incorrect answers (24 hours off).

But your tblInfantone! syntax may be the source of the problem. What's the
Recordsource of your form? Is it a query joining both tables? If so could you
post the SQL view of the query?

DateDiff("n"...) will give the time difference in integer minutes. If you
want to see hours and minutes use a syntax such as

DateDiff("n", [Time_Of_Birth], [Time_of_placenta_removal]) \ 60 &
Format(DateDiff("n", [Time_Of_Birth], [Time_of_placenta_removal]) MOD 60,
"\:00")

 

[kc-mass]

I believe you need to open a second recordset to act on the second tables
data.
See suggested changes below:
Regards

Kevin

It puts 0:00 in te field space and then 1.47 comes up when i place the
mouse
over it. The answer should be 3 hrs
The start and end times are in two different tables but one form

Private Sub timecheck()
Dim db As Database
Dim rs As Recordset Dim rs2 as Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblinfantone")

Set rs2 = db.Openrecordset("tblMaternal")

rs.MoveFirst

'Assuming they are both in the first record of the dataset
rs2.Movefirst
Debug.Print Format(rs![Date_OF_Birth] - 1 -
rs2![Date_of_Placenta_removal], "Short Time")

Debug.Print Format(rs![ tblinfantone]![Date_OF_Birth] - 1 - rs![
tblMaternal]![Date_of_Placenta_removal], "Short Time")
End Sub

Assuming you have a table (can also be form controls) with date/time
fields
of
"start" and "end", try something like this:

Sub timecheck()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTime")
rs.MoveFirst
Debug.Print Format(rs![Start] - 1 - rs![End], "Short Time")
End Sub

Times of 11:23 AM and 1:30 PM will yield "02:07"

Regards

Kevin

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