Calculate time differences in Access

[QHRider]

I am trying to figure out how to calculate time differences in Access and be able to show an improvement or not in minutes and/or seconds. For example:

Spring 06 Mile Run Time: 13:06
Fall 07 Mile Run Time: 12:56

Result I would like to see is: -0:10 (10 seconds less)

Any suggestions?

Thanks,
QHRider

 

[SkipVought]

Hi,

DateDiff() in Seconds.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[missinglinq]

SecondsDifferent = datediff("s",FirstTime,SecondTime))/60

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[dhookom]

Missinglinq,
I'm not sure why you would divide the seconds by 60...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[missinglinq]

Gives you an answer in seconds. Given the data as presented, you don't actually have minutes and seconds!

13:06 in time is not actually 13 minutes and 6 seconds, it's 13 hundred hours and 6 minutes! Likewise 12:56 is 12 hundred hours and 56 minutes. Using

DateDiff("s","13:06", "12:56")

will yields -600 seconds or one minute, which is not correct!

You could, of course, add "00:" in front of each time given, making them 00:13:56 and 00:12:56 respectively, and DateDiff would work just fine, but why go thru that hassle? If we were dealing with differences in times where the times ran into hours and minutes and seconds it would be a different mater, granted.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[dhookom]

If this was a "mile run" then 13:06 if stored in a datetime field is actually 00:13:06 without displaying the hours. I doubt the time was 13 hours and 6 minutes.

DateDiff("s",#00:12:56#,#00:13:06#) = 10

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[missinglinq]

Good point!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!