calculated field - time

[G.]

My form has a start time field and an end time field. Both fields are
formatted as Medium Time. I have 2 questions:

1) I need to calculate the difference in time between the 2 fields. The
current formula is =[start time]-[end time]. The formatting for this
calculated field is n. The problem is that this formula does fine when the
times are less than 61 minutes apart. However, if the start time is 10:35am
and the end time is 12:10pm the data in the calculated field returns a value
of 35? Also, if the end time is earlier than the start time (yes, it is
possible in my situation), the data in the calcualted field still returns the
time difference as positive.

2) I have a conditional field on the same form based on the answer to the
time difference field described above. I want the field to return "Yes" if
the time difference is greater than 60 minutes and "No" if it is less. The
curretn formula is =IIf([time diff]>60,"Yes","No"). The problem is that
nothing is greater than 60 even if the time difference is more than 1 hr.
apart.

This is probably an easy fix but I just can't get my head around it. Please
Please help.

Thank you.

 

[J. Blauth]

Hi G,

My form has a start time field and an end time field. Both fields are
formatted as Medium Time. I have 2 questions:

1) I need to calculate the difference in time between the 2 fields. The
current formula is =[start time]-[end time]. The formatting for this
calculated field is n. The problem is that this formula does fine when the
times are less than 61 minutes apart. However, if the start time is 10:35am
and the end time is 12:10pm the data in the calculated field returns a value
of 35? Also, if the end time is earlier than the start time (yes, it is
possible in my situation), the data in the calcualted field still returns the
time difference as positive.

Datediff(n, [start time], [end time])
returns you the number of minutes between start time and end time.

2) I have a conditional field on the same form based on the answer to the
time difference field described above. I want the field to return "Yes" if
the time difference is greater than 60 minutes and "No" if it is less. The
curretn formula is =IIf([time diff]>60,"Yes","No"). The problem is that
nothing is greater than 60 even if the time difference is more than 1 hr.
apart.

 

[G.]

Thanks J. Blauth.

I actually tried that datediff formula but it gives me an error (#Name?) in
the fileds. Also, when I type in the formula it wraps the n in brackets as
follows =DateDiff([n],[start time],[end]).

It's strange to me because it works perfectly fine in a query where my
formula is DateDiff("n",[start time],[end time]).

It's really been frustrating me. I'm ready to choke the life out of MS
Access. Any other advice or suggestions are very very welcome.

Thank you.

Hi G,

My form has a start time field and an end time field. Both fields are
formatted as Medium Time. I have 2 questions:

1) I need to calculate the difference in time between the 2 fields. The
current formula is =[start time]-[end time]. The formatting for this
calculated field is n. The problem is that this formula does fine when the
times are less than 61 minutes apart. However, if the start time is 10:35am
and the end time is 12:10pm the data in the calculated field returns a value
of 35? Also, if the end time is earlier than the start time (yes, it is
possible in my situation), the data in the calcualted field still returns the
time difference as positive.

Datediff(n, [start time], [end time])
returns you the number of minutes between start time and end time.

2) I have a conditional field on the same form based on the answer to the
time difference field described above. I want the field to return "Yes" if
the time difference is greater than 60 minutes and "No" if it is less. The
curretn formula is =IIf([time diff]>60,"Yes","No"). The problem is that
nothing is greater than 60 even if the time difference is more than 1 hr.
apart.

This is probably an easy fix but I just can't get my head around it. Please
Please help.

Thank you.

 

[Douglas J. Steele]

Try putting quotes around the n in your ControlSource, just as you did in
your query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks J. Blauth.

I actually tried that datediff formula but it gives me an error (#Name?)
in
the fileds. Also, when I type in the formula it wraps the n in brackets as
follows =DateDiff([n],[start time],[end]).

It's strange to me because it works perfectly fine in a query where my
formula is DateDiff("n",[start time],[end time]).

It's really been frustrating me. I'm ready to choke the life out of MS
Access. Any other advice or suggestions are very very welcome.

Thank you.

Hi G,

My form has a start time field and an end time field. Both fields are
formatted as Medium Time. I have 2 questions:

1) I need to calculate the difference in time between the 2 fields. The
current formula is =[start time]-[end time]. The formatting for this
calculated field is n. The problem is that this formula does fine when
the
times are less than 61 minutes apart. However, if the start time is
10:35am
and the end time is 12:10pm the data in the calculated field returns a
value
of 35? Also, if the end time is earlier than the start time (yes, it is
possible in my situation), the data in the calcualted field still
returns the
time difference as positive.

Datediff(n, [start time], [end time])
returns you the number of minutes between start time and end time.

2) I have a conditional field on the same form based on the answer to
the
time difference field described above. I want the field to return "Yes"
if
the time difference is greater than 60 minutes and "No" if it is less.
The
curretn formula is =IIf([time diff]>60,"Yes","No"). The problem is that
nothing is greater than 60 even if the time difference is more than 1
hr.
apart.

This is probably an easy fix but I just can't get my head around it.
Please
Please help.

Thank you.