Time Format and Calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Back Again,
I have a form or a report (Preferred). I have an Access Database that
Automatically enters the System Time (Now()) when a User Logs in and again
when they Log Out. Now I am attempting to get Access either in a Query, Form
or Report to give me the Difference between the TimeIn and the Time Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have searched the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or they
simply do not work (I assume to first since I am not an expert at access, nor
a complete novice). I need a way to format a Query, Form and or Report so it
gives me the difference between a TimeIn field and a TimeOut field both using
a Now() statement for the input in to the Table. I also need to know how to
format the Code, Macro etc and where I should place it to make it work.
Thanks again,
 
in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or report on the
query, using the TotalTime field to display the "h:nn" (n = minutes). or you
can use the expression in a calculated control directly in a form or report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h", [TimeIn],
[TimeOut]) Mod 60), "00")

hth
 
Doug and Tina,
Thanks I have attempted all three and they do work to an extent. They
calculate both the Hours and Minutes as Hours. Here is what I have and what I
got from all three suggestions:

I have the following

1 Record TimeIn 06:16 TimeOut:07:18 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:18 TimeOut:07:29 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:24 TimeOut:11:51 Igot all 3 time TotalTime:5:05
1 Record TimeIn 06:26 TimeOut:13:15 Igot all 3 time TotalTime:7:07

So the Hours part works great, but I need the Minutes as well for our
Reports. any suggetsions. Doug I marked you wedsite for future referance.
Thanks.

tina said:
in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or report on the
query, using the TotalTime field to display the "h:nn" (n = minutes). or you
can use the expression in a calculated control directly in a form or report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h", [TimeIn],
[TimeOut]) Mod 60), "00")

hth


Dr. C said:
Back Again,
I have a form or a report (Preferred). I have an Access Database that
Automatically enters the System Time (Now()) when a User Logs in and again
when they Log Out. Now I am attempting to get Access either in a Query, Form
or Report to give me the Difference between the TimeIn and the Time Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have searched the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or they
simply do not work (I assume to first since I am not an expert at access, nor
a complete novice). I need a way to format a Query, Form and or Report so it
gives me the difference between a TimeIn field and a TimeOut field both using
a Now() statement for the input in to the Table. I also need to know how to
format the Code, Macro etc and where I should place it to make it work.
Thanks again,
Click to expand...
Click to expand...
 
on testing, i got exactly the same results. that surpasses odd.
So the Hours part works great
Click to expand...

actually, it doesn't. on your last example, the hours should be 6, not 7.
i have no idea why the expressions didn't work. Doug's and mine are nearly
identical, and obviously we each thought our solution would work.

at any rate, i fiddled around and came up with a function that does return
the correct values. at least, it did in my test! <g>

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As String

Dim intMin As Integer
intMin = DateDiff("n", datIn, datOut)
isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

paste the above into a standard module in your database (if you have to
create a new module, name the module anything *except* the name of the
function).

use the function in a query, as

TotalTime: IsTime([TimeIn], [TimeOut])

or directly in a form/report control, as

=IsTime([TimeIn], [TimeOut])

note that this function (just as the earlier expressions) don't take seconds
into account. so if your In time is 6:16:59 and your Out time is 7:18:01,
the function returns 1:02, even though it's actually 1 hour and *just 2
seconds over 1 minute*.

that kind of discrepancy may add up significantly after awhile. if you don't
want a minute counted unless it's a full 60 seconds, use

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As String

Dim intMin As Integer
intMin = Int(DateDiff("s", datIn, datOut) / 60)

isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

hth


Dr. C said:
Doug and Tina,
Thanks I have attempted all three and they do work to an extent. They
calculate both the Hours and Minutes as Hours. Here is what I have and what I
got from all three suggestions:

I have the following

1 Record TimeIn 06:16 TimeOut:07:18 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:18 TimeOut:07:29 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:24 TimeOut:11:51 Igot all 3 time TotalTime:5:05
1 Record TimeIn 06:26 TimeOut:13:15 Igot all 3 time TotalTime:7:07

So the Hours part works great, but I need the Minutes as well for our
Reports. any suggetsions. Doug I marked you wedsite for future referance.
Thanks.

tina said:
in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or report on the
query, using the TotalTime field to display the "h:nn" (n = minutes). or you
can use the expression in a calculated control directly in a form or report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h", [TimeIn],
[TimeOut]) Mod 60), "00")

hth


Dr. C said:
Back Again,
I have a form or a report (Preferred). I have an Access Database that
Automatically enters the System Time (Now()) when a User Logs in and again
when they Log Out. Now I am attempting to get Access either in a
Click to expand...
Query,
Form
or Report to give me the Difference between the TimeIn and the Time Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have
Click to expand...
searched
the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or they
simply do not work (I assume to first since I am not an expert at
Click to expand...
access,
nor
a complete novice). I need a way to format a Query, Form and or Report
Click to expand...
so
it
gives me the difference between a TimeIn field and a TimeOut field
Click to expand...
both
using
a Now() statement for the input in to the Table. I also need to know
Click to expand...
how
to
format the Code, Macro etc and where I should place it to make it work.
Thanks again,
Click to expand...
Click to expand...
Click to expand...
 
Tina,
Your beautiful and wonderful, this will save the day tomorrow at my Opening
of the new Program. Unfortuneatly there no cash incentive involved or I'd
spit it with you. All i can ofer is a VERY BIG THANKS.

DR. C

tina said:
on testing, i got exactly the same results. that surpasses odd.
So the Hours part works great
Click to expand...

actually, it doesn't. on your last example, the hours should be 6, not 7.
i have no idea why the expressions didn't work. Doug's and mine are nearly
identical, and obviously we each thought our solution would work.

at any rate, i fiddled around and came up with a function that does return
the correct values. at least, it did in my test! <g>

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As String

Dim intMin As Integer
intMin = DateDiff("n", datIn, datOut)
isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

paste the above into a standard module in your database (if you have to
create a new module, name the module anything *except* the name of the
function).

use the function in a query, as

TotalTime: IsTime([TimeIn], [TimeOut])

or directly in a form/report control, as

=IsTime([TimeIn], [TimeOut])

note that this function (just as the earlier expressions) don't take seconds
into account. so if your In time is 6:16:59 and your Out time is 7:18:01,
the function returns 1:02, even though it's actually 1 hour and *just 2
seconds over 1 minute*.

that kind of discrepancy may add up significantly after awhile. if you don't
want a minute counted unless it's a full 60 seconds, use

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As String

Dim intMin As Integer
intMin = Int(DateDiff("s", datIn, datOut) / 60)

isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

hth


Dr. C said:
Doug and Tina,
Thanks I have attempted all three and they do work to an extent. They
calculate both the Hours and Minutes as Hours. Here is what I have and what I
got from all three suggestions:

I have the following

1 Record TimeIn 06:16 TimeOut:07:18 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:18 TimeOut:07:29 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:24 TimeOut:11:51 Igot all 3 time TotalTime:5:05
1 Record TimeIn 06:26 TimeOut:13:15 Igot all 3 time TotalTime:7:07

So the Hours part works great, but I need the Minutes as well for our
Reports. any suggetsions. Doug I marked you wedsite for future referance.
Thanks.

tina said:
in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or report on the
query, using the TotalTime field to display the "h:nn" (n = minutes). or you
can use the expression in a calculated control directly in a form or report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h", [TimeIn],
[TimeOut]) Mod 60), "00")

hth


Back Again,
I have a form or a report (Preferred). I have an Access Database that
Automatically enters the System Time (Now()) when a User Logs in and again
when they Log Out. Now I am attempting to get Access either in a Query,
Form
or Report to give me the Difference between the TimeIn and the Time Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have searched
the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or they
simply do not work (I assume to first since I am not an expert at access,
nor
a complete novice). I need a way to format a Query, Form and or Report so
it
gives me the difference between a TimeIn field and a TimeOut field both
using
a Now() statement for the input in to the Table. I also need to know how
to
format the Code, Macro etc and where I should place it to make it work.
Thanks again,
Click to expand...
Click to expand...
Click to expand...
 
Actually, we both made errors, Tina!

I had division, rather than mod, for figuring out the minutes. You used "h"
as the interval, rather than "n".

Using the following formula:

DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")

I get

Start 06:16, End 07:18
1:02

Start 06:16, End 07:29
1:13

Start 06:24, End 11:51
5:27

Start 06:26, End 13:15
6:49

And that can actually be simplified to

DateDiff("h", [TimeIn], [TimeOut]) & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")


Sorry for the confusion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



tina said:
on testing, i got exactly the same results. that surpasses odd.
So the Hours part works great
Click to expand...

actually, it doesn't. on your last example, the hours should be 6, not 7.
i have no idea why the expressions didn't work. Doug's and mine are nearly
identical, and obviously we each thought our solution would work.

at any rate, i fiddled around and came up with a function that does return
the correct values. at least, it did in my test! <g>

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = DateDiff("n", datIn, datOut)
isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

paste the above into a standard module in your database (if you have to
create a new module, name the module anything *except* the name of the
function).

use the function in a query, as

TotalTime: IsTime([TimeIn], [TimeOut])

or directly in a form/report control, as

=IsTime([TimeIn], [TimeOut])

note that this function (just as the earlier expressions) don't take
seconds
into account. so if your In time is 6:16:59 and your Out time is 7:18:01,
the function returns 1:02, even though it's actually 1 hour and *just 2
seconds over 1 minute*.

that kind of discrepancy may add up significantly after awhile. if you
don't
want a minute counted unless it's a full 60 seconds, use

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = Int(DateDiff("s", datIn, datOut) / 60)

isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

hth


Dr. C said:
Doug and Tina,
Thanks I have attempted all three and they do work to an extent. They
calculate both the Hours and Minutes as Hours. Here is what I have and what I
got from all three suggestions:

I have the following

1 Record TimeIn 06:16 TimeOut:07:18 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:18 TimeOut:07:29 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:24 TimeOut:11:51 Igot all 3 time TotalTime:5:05
1 Record TimeIn 06:26 TimeOut:13:15 Igot all 3 time TotalTime:7:07

So the Hours part works great, but I need the Minutes as well for our
Reports. any suggetsions. Doug I marked you wedsite for future referance.
Thanks.

tina said:
in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or report on the
query, using the TotalTime field to display the "h:nn" (n = minutes).
or you
can use the expression in a calculated control directly in a form or report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h", [TimeIn],
[TimeOut]) Mod 60), "00")

hth


Back Again,
I have a form or a report (Preferred). I have an Access Database
that
Automatically enters the System Time (Now()) when a User Logs in and again
when they Log Out. Now I am attempting to get Access either in a Query,
Form
or Report to give me the Difference between the TimeIn and the Time Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have searched
the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or they
simply do not work (I assume to first since I am not an expert at access,
nor
a complete novice). I need a way to format a Query, Form and or
Report so
it
gives me the difference between a TimeIn field and a TimeOut field both
using
a Now() statement for the input in to the Table. I also need to know how
to
format the Code, Macro etc and where I should place it to make it work.
Thanks again,
Click to expand...
Click to expand...
Click to expand...
 
oops - we both had an attack of butterfingers, didn't we? that's a bizarre
coincidence! my excuse is that i've been up since yesterday, what's yours?
<g>

when i tried your expression
DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")
all the times came out right, as you noted.

but when i ran the "simplified" equation (which was actually what i *meant*
to type in my first post), the time for the last group
Start 06:26, End 13:15
came back incorrectly, as
7:49
so i was just wrong all the way around!

now my question is: is there a difference, time-wise/performance-wise, to
running the DateDiff function twice in every query record - as opposed to
having to call out the custom function and pass values to it? (and of course
the function uses DateDiff, too.) or is it a "six of one, half a dozen of
the other" tossup?


Douglas J. Steele said:
Actually, we both made errors, Tina!

I had division, rather than mod, for figuring out the minutes. You used "h"
as the interval, rather than "n".

Using the following formula:

DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")

I get

Start 06:16, End 07:18
1:02

Start 06:16, End 07:29
1:13

Start 06:24, End 11:51
5:27

Start 06:26, End 13:15
6:49

And that can actually be simplified to

DateDiff("h", [TimeIn], [TimeOut]) & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")


Sorry for the confusion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



tina said:
on testing, i got exactly the same results. that surpasses odd.
So the Hours part works great
Click to expand...

actually, it doesn't. on your last example, the hours should be 6, not 7.
i have no idea why the expressions didn't work. Doug's and mine are nearly
identical, and obviously we each thought our solution would work.

at any rate, i fiddled around and came up with a function that does return
the correct values. at least, it did in my test! <g>

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = DateDiff("n", datIn, datOut)
isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

paste the above into a standard module in your database (if you have to
create a new module, name the module anything *except* the name of the
function).

use the function in a query, as

TotalTime: IsTime([TimeIn], [TimeOut])

or directly in a form/report control, as

=IsTime([TimeIn], [TimeOut])

note that this function (just as the earlier expressions) don't take
seconds
into account. so if your In time is 6:16:59 and your Out time is 7:18:01,
the function returns 1:02, even though it's actually 1 hour and *just 2
seconds over 1 minute*.

that kind of discrepancy may add up significantly after awhile. if you
don't
want a minute counted unless it's a full 60 seconds, use

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = Int(DateDiff("s", datIn, datOut) / 60)

isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

hth


Dr. C said:
Doug and Tina,
Thanks I have attempted all three and they do work to an extent. They
calculate both the Hours and Minutes as Hours. Here is what I have and what I
got from all three suggestions:

I have the following

1 Record TimeIn 06:16 TimeOut:07:18 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:18 TimeOut:07:29 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:24 TimeOut:11:51 Igot all 3 time TotalTime:5:05
1 Record TimeIn 06:26 TimeOut:13:15 Igot all 3 time TotalTime:7:07

So the Hours part works great, but I need the Minutes as well for our
Reports. any suggetsions. Doug I marked you wedsite for future referance.
Thanks.

:

in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or report
Click to expand...
on
the
query, using the TotalTime field to display the "h:nn" (n = minutes).
or you
can use the expression in a calculated control directly in a form or report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h", [TimeIn],
[TimeOut]) Mod 60), "00")

hth


Back Again,
I have a form or a report (Preferred). I have an Access Database
that
Automatically enters the System Time (Now()) when a User Logs in
Click to expand...
and
again
when they Log Out. Now I am attempting to get Access either in a Query,
Form
or Report to give me the Difference between the TimeIn and the Time Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have searched
the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or they
simply do not work (I assume to first since I am not an expert at access,
nor
a complete novice). I need a way to format a Query, Form and or
Report so
it
gives me the difference between a TimeIn field and a TimeOut field both
using
a Now() statement for the input in to the Table. I also need to
Click to expand...
know
how
to
format the Code, Macro etc and where I should place it to make it work.
Thanks again,
Click to expand...
Click to expand...
Click to expand...
 
Doug,
I tried your new one and it also worked perfectly. I haveded it to my list
og Access Commands to remember (My word document for remembering). Thanks to
both of you for the wonderful assistance. Until next time.

DR. C

tina said:
oops - we both had an attack of butterfingers, didn't we? that's a bizarre
coincidence! my excuse is that i've been up since yesterday, what's yours?
<g>

when i tried your expression
DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")
all the times came out right, as you noted.

but when i ran the "simplified" equation (which was actually what i *meant*
to type in my first post), the time for the last group
Start 06:26, End 13:15
came back incorrectly, as
7:49
so i was just wrong all the way around!

now my question is: is there a difference, time-wise/performance-wise, to
running the DateDiff function twice in every query record - as opposed to
having to call out the custom function and pass values to it? (and of course
the function uses DateDiff, too.) or is it a "six of one, half a dozen of
the other" tossup?


Douglas J. Steele said:
Actually, we both made errors, Tina!

I had division, rather than mod, for figuring out the minutes. You used "h"
as the interval, rather than "n".

Using the following formula:

DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")

I get

Start 06:16, End 07:18
1:02

Start 06:16, End 07:29
1:13

Start 06:24, End 11:51
5:27

Start 06:26, End 13:15
6:49

And that can actually be simplified to

DateDiff("h", [TimeIn], [TimeOut]) & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")


Sorry for the confusion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



tina said:
on testing, i got exactly the same results. that surpasses odd.

So the Hours part works great

actually, it doesn't. on your last example, the hours should be 6, not 7.
i have no idea why the expressions didn't work. Doug's and mine are nearly
identical, and obviously we each thought our solution would work.

at any rate, i fiddled around and came up with a function that does return
the correct values. at least, it did in my test! <g>

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = DateDiff("n", datIn, datOut)
isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

paste the above into a standard module in your database (if you have to
create a new module, name the module anything *except* the name of the
function).

use the function in a query, as

TotalTime: IsTime([TimeIn], [TimeOut])

or directly in a form/report control, as

=IsTime([TimeIn], [TimeOut])

note that this function (just as the earlier expressions) don't take
seconds
into account. so if your In time is 6:16:59 and your Out time is 7:18:01,
the function returns 1:02, even though it's actually 1 hour and *just 2
seconds over 1 minute*.

that kind of discrepancy may add up significantly after awhile. if you
don't
want a minute counted unless it's a full 60 seconds, use

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = Int(DateDiff("s", datIn, datOut) / 60)

isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

hth


Doug and Tina,
Thanks I have attempted all three and they do work to an extent. They
calculate both the Hours and Minutes as Hours. Here is what I have and
what I
got from all three suggestions:

I have the following

1 Record TimeIn 06:16 TimeOut:07:18 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:18 TimeOut:07:29 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:24 TimeOut:11:51 Igot all 3 time TotalTime:5:05
1 Record TimeIn 06:26 TimeOut:13:15 Igot all 3 time TotalTime:7:07

So the Hours part works great, but I need the Minutes as well for our
Reports. any suggetsions. Doug I marked you wedsite for future referance.
Thanks.

:

in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" &
Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or report on
the
query, using the TotalTime field to display the "h:nn" (n = minutes).
or
you
can use the expression in a calculated control directly in a form or
report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn],
[TimeOut]) Mod 60), "00")

hth


Back Again,
I have a form or a report (Preferred). I have an Access Database
that
Automatically enters the System Time (Now()) when a User Logs in and
again
when they Log Out. Now I am attempting to get Access either in a
Query,
Form
or Report to give me the Difference between the TimeIn and the Time
Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have
searched
the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or
they
simply do not work (I assume to first since I am not an expert at
access,
nor
a complete novice). I need a way to format a Query, Form and or
Report
so
it
gives me the difference between a TimeIn field and a TimeOut field
both
using
a Now() statement for the input in to the Table. I also need to know
how
to
format the Code, Macro etc and where I should place it to make it
work.
Thanks again,
Click to expand...
Click to expand...
Click to expand...
 
you're welcome :)


Dr. C said:
Doug,
I tried your new one and it also worked perfectly. I haveded it to my list
og Access Commands to remember (My word document for remembering). Thanks to
both of you for the wonderful assistance. Until next time.

DR. C

tina said:
oops - we both had an attack of butterfingers, didn't we? that's a bizarre
coincidence! my excuse is that i've been up since yesterday, what's yours?
<g>

when i tried your expression
DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")
all the times came out right, as you noted.

but when i ran the "simplified" equation (which was actually what i *meant*
to type in my first post), the time for the last group
Start 06:26, End 13:15
came back incorrectly, as
7:49
so i was just wrong all the way around!

now my question is: is there a difference, time-wise/performance-wise, to
running the DateDiff function twice in every query record - as opposed to
having to call out the custom function and pass values to it? (and of course
the function uses DateDiff, too.) or is it a "six of one, half a dozen of
the other" tossup?


Douglas J. Steele said:
Actually, we both made errors, Tina!

I had division, rather than mod, for figuring out the minutes. You
Click to expand...
used
"h"
as the interval, rather than "n".

Using the following formula:

DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")

I get

Start 06:16, End 07:18
1:02

Start 06:16, End 07:29
1:13

Start 06:24, End 11:51
5:27

Start 06:26, End 13:15
6:49

And that can actually be simplified to

DateDiff("h", [TimeIn], [TimeOut]) & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")


Sorry for the confusion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



on testing, i got exactly the same results. that surpasses odd.

So the Hours part works great

actually, it doesn't. on your last example, the hours should be 6,
Click to expand...
not
7.
i have no idea why the expressions didn't work. Doug's and mine are nearly
identical, and obviously we each thought our solution would work.

at any rate, i fiddled around and came up with a function that does return
the correct values. at least, it did in my test! <g>

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = DateDiff("n", datIn, datOut)
isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

paste the above into a standard module in your database (if you have to
create a new module, name the module anything *except* the name of the
function).

use the function in a query, as

TotalTime: IsTime([TimeIn], [TimeOut])

or directly in a form/report control, as

=IsTime([TimeIn], [TimeOut])

note that this function (just as the earlier expressions) don't take
seconds
into account. so if your In time is 6:16:59 and your Out time is 7:18:01,
the function returns 1:02, even though it's actually 1 hour and *just 2
seconds over 1 minute*.

that kind of discrepancy may add up significantly after awhile. if you
don't
want a minute counted unless it's a full 60 seconds, use

Public Function isTime(ByVal datIn As Date, ByVal datOut As Date) As
String

Dim intMin As Integer
intMin = Int(DateDiff("s", datIn, datOut) / 60)

isTime = Int(intMin / 60) & ":" & Format((intMin Mod 60), "00")

End Function

hth


Doug and Tina,
Thanks I have attempted all three and they do work to an extent. They
calculate both the Hours and Minutes as Hours. Here is what I have and
what I
got from all three suggestions:

I have the following

1 Record TimeIn 06:16 TimeOut:07:18 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:18 TimeOut:07:29 Igot all 3 time TotalTime:1:01
1 Record TimeIn 06:24 TimeOut:11:51 Igot all 3 time TotalTime:5:05
1 Record TimeIn 06:26 TimeOut:13:15 Igot all 3 time TotalTime:7:07

So the Hours part works great, but I need the Minutes as well for our
Reports. any suggetsions. Doug I marked you wedsite for future referance.
Thanks.

:

in a query, try

TotalTime: DateDiff("h", [TimeIn], [TimeOut]) & ":" &
Format((DateDiff("h",
[TimeIn], [TimeOut]) Mod 60), "00")

(the above all goes on one line.) you can then base a form or
Click to expand...
report
on
the
query, using the TotalTime field to display the "h:nn" (n = minutes).
or
you
can use the expression in a calculated control directly in a form or
report,
by putting it in the control's ControlSource, as

=DateDiff("h", [TimeIn], [TimeOut]) & ":" & Format((DateDiff("h",
[TimeIn],
[TimeOut]) Mod 60), "00")

hth


Back Again,
I have a form or a report (Preferred). I have an Access Database
that
Automatically enters the System Time (Now()) when a User Logs
Click to expand...
in
and
again
when they Log Out. Now I am attempting to get Access either in a
Query,
Form
or Report to give me the Difference between the TimeIn and the Time
Out.
After about 2 Hours I finally got a Query and a Form to give me the
Calculation in Minutes, but I need an Hour:Minute format. I have
searched
the
Help Area, and the Internet and have attempted several formatting
suggestions, but I am either not adding them to the correct area or
they
simply do not work (I assume to first since I am not an expert at
access,
nor
a complete novice). I need a way to format a Query, Form and or
Report
so
it
gives me the difference between a TimeIn field and a TimeOut field
both
using
a Now() statement for the input in to the Table. I also need to know
how
to
format the Code, Macro etc and where I should place it to make it
work.
Thanks again,
Click to expand...
Click to expand...
Click to expand...
 
I should have stopped while I was ahead! (To be honest, I didn't bother
testing the "simplified" equation very extensively, but it makes sense that
it wouldn't work for the example you gave for the same reason that using
DateDiff("yyyy", DOB, Date()) doesn't work).

As to the performance issue, why not test the two approaches in a loop and
see whether there's an appreciable difference? My suspicion is that there
shouldn't be much of a difference, although I'd expect the two calls to
DateDiff to be slightly faster than using a user-defined function because it
makes sense to me that using internal functions should involve slightly less
overhead than UDFs.

I generally try to avoid using UDFs in queries simply because they cannot be
used from outside of Access (say from VB or an ASP page). However, I'm not
positive that DateDiff can be used from outside of Access either, so it
might be a moot point.
 
ah, good points, thanks Doug! :)


Douglas J. Steele said:
I should have stopped while I was ahead! (To be honest, I didn't bother
testing the "simplified" equation very extensively, but it makes sense that
it wouldn't work for the example you gave for the same reason that using
DateDiff("yyyy", DOB, Date()) doesn't work).

As to the performance issue, why not test the two approaches in a loop and
see whether there's an appreciable difference? My suspicion is that there
shouldn't be much of a difference, although I'd expect the two calls to
DateDiff to be slightly faster than using a user-defined function because it
makes sense to me that using internal functions should involve slightly less
overhead than UDFs.

I generally try to avoid using UDFs in queries simply because they cannot be
used from outside of Access (say from VB or an ASP page). However, I'm not
positive that DateDiff can be used from outside of Access either, so it
might be a moot point.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



tina said:
oops - we both had an attack of butterfingers, didn't we? that's a bizarre
coincidence! my excuse is that i've been up since yesterday, what's yours?
<g>

when i tried your expression
DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & _
Format(DateDiff("n", [TimeIn], [TimeOut]) Mod 60, "00")
all the times came out right, as you noted.

but when i ran the "simplified" equation (which was actually what i
*meant*
to type in my first post), the time for the last group
Start 06:26, End 13:15
came back incorrectly, as
7:49
so i was just wrong all the way around!

now my question is: is there a difference, time-wise/performance-wise, to
running the DateDiff function twice in every query record - as opposed to
having to call out the custom function and pass values to it? (and of
course
the function uses DateDiff, too.) or is it a "six of one, half a dozen of
the other" tossup?
Click to expand...
Click to expand...
 
Back
Top