When the Second Time Crosses Midnight?

[Iain Scott]

Hi,

I am trying to do a query (in ACCESS'97) part of which counts the
number of hours:minutes from one time to another.

I'm doing =[time2]-[time1]

How do I do this when the second time crosses midnight? I find I get
an aberrant figure ...

EXAMPLE: TIME1 TIME2 DIFFERENCE
19:30 21:00 01:30 - correct
21:10 00:40 20:30 - wrong

Thanks anyone who can help!!!

Iain

 

[Michel Walsh]

Hi,


If you keep the date with your time, you don't get any problem.

You can add 1 ( 1=1day = 24 hour) to a negative result:

iif( tme1>time2, 1+time2-time1, time2-time1)


that assumes you can't have more than one day between the two times.



Hoping it may help,
Vanderghast, Access MVP.

 

[Iain Scott]

Thank you vanderghast!

I will try this out tonight and see if it works.

Iain

Hi,


If you keep the date with your time, you don't get any problem.

You can add 1 ( 1=1day = 24 hour) to a negative result:

iif( tme1>time2, 1+time2-time1, time2-time1)


that assumes you can't have more than one day between the two times.



Hoping it may help,
Vanderghast, Access MVP.

Hi,

I am trying to do a query (in ACCESS'97) part of which counts the
number of hours:minutes from one time to another.

I'm doing =[time2]-[time1]

How do I do this when the second time crosses midnight? I find I get
an aberrant figure ...

EXAMPLE: TIME1 TIME2 DIFFERENCE
19:30 21:00 01:30 - correct
21:10 00:40 20:30 - wrong

Thanks anyone who can help!!!

Iain

 

[Iain Scott]

Hi Mr.Walsh,

This solution did work and I appreciate your help there with the
problem.

Iain Scott

Hi,


If you keep the date with your time, you don't get any problem.

You can add 1 ( 1=1day = 24 hour) to a negative result:

iif( tme1>time2, 1+time2-time1, time2-time1)


that assumes you can't have more than one day between the two times.



Hoping it may help,
Vanderghast, Access MVP.

Hi,

I am trying to do a query (in ACCESS'97) part of which counts the
number of hours:minutes from one time to another.

I'm doing =[time2]-[time1]

How do I do this when the second time crosses midnight? I find I get
an aberrant figure ...

EXAMPLE: TIME1 TIME2 DIFFERENCE
19:30 21:00 01:30 - correct
21:10 00:40 20:30 - wrong

Thanks anyone who can help!!!

Iain