Date and time function

[Joe8915]

Very Simple Query using the date and time function

Trying to show the total hours work.

My result is:
Start End Total Hrs Work

5/3/2011 9:45:00 AM 5/3/2011 5:00:00 PM 7


It should read:
Start End Total Hrs Work

5/3/2011 9:45:00 AM 5/3/2011 5:00:00 PM 6 hrs 45 mins



My Qry desing is:
Total Hrs Work: DateDiff("h",[Strttime],[EndTime])-1

the -1 is for 1 hour for lunch

What is it I am doing wrong?

 

[John Big Booty]

Your DateDiff() is Using "h" which will return hours, I would have expected your Query to return a result of 6.75 hours, so given that it is actually returning 7 hours, I'm guessing the field is set as an integer and is rounding the result up to 7 hours. To return a result of 6 Hours and 45 Minutes you will need to manipulate your result of 6.75 to break it down to it's hours and minutes.

 

[pbaldy]

If memory serves, DateDiff will not return a fractional value. Check out the first two links here:

http://www.pacificdb.com.au/Support/code_datetime.htm

 

[John Big Booty]

My bad; Paul is absolutely correct

Just been playing in my sandbox DB and found that DateDiff("h",[Strttime],[EndTime])-1 should only return the number of full hours ie. it should round down to the last full hour. To get the number of minutes use;

60*((DateDiff("n",[Strttime],[EndTime])/60) -DateDiff("h",[Strttime],[EndTime]))

 

[Joe8915]

Thanks all for the quick reply. I am still not getting the result that I am looking for.
I pasted the following code into the qry
60*((DateDiff("n",[Strttime],[EndTime])/60)-DateDiff("h",[Strttime],[EndTime]))
And I get the -30 which is correct, but how do I get it to where it reads
6hrs and 45 minutes

Here is my Start time
5/3/2011 9:30:00 AM
Here is my End time
5/3/2011 5:00:00 PM

What is it that I am missing?
Hey its a Tuesday after a holiday what can I say.

 

[Joe8915]

OK guys, I finally got it to work. this is what you all were trying to explain to me.
First I had to add another field for minutes
Minutes: DateDiff("n",[Strttime],[EndTime])
Then add
Total Hrs Work: [Minutes]\60 & Format([Minutes] Mod 60,"\:00"). Thanks to the both of you for helping me on this