Time-keeping with Access 2000

[Michael Bulatovich]

I have a very simple db I use for keeping track of hours, tasks, projects,
clients etc. It has a form that I use to enter data. Currently the form has
a textbox for a field called "start time", another for a field called "end
time" and a third for a field called "hours", among other controls. I now
type in the approximate times in the first two textboxes, and mentally
calculate and enter the difference or elapsed time in the third.

I have tried to automate the completion of this third field with no luck. I
have tried entering an expression in the control source field of the text
box. I have tried stuff like "=[end time]-[start time]" and "=([end
time]-[start time]*24)" but I'm clearly lost. The first two textboxes are
formatted for "short time". "hours" is formatted as a "general number".

I would like to have the completion of the first two textboxes yield their
difference in the "hours" field in the underlying table, and display in the
form textbox associated with this field. Some other considerations are:

Spanning midnight shouldn't be a problem.

I would like to round the hours to the nearest quarter, since I'm
approximating the time anyway.

I would appreciate any help anyone could extend. One more thing: is possible
to get a control (button) to record the current time in a field?
TIA
--

MichaelB
www.michaelbulatovich.com

[Allen Browne]

For basic info on how to calculate the difference between two date/time
fields, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For rounding to the nearest 15 minutes, use:
15 * Round([Minutes] / 15, 0)

In your table, store the starting time and EITHER the ending time OR the
duration. Storing both violates a basic rule of data normalization. The
other one can be calculated in a query, as explained in the article above.

To pop the current time into a text box named "StartTime":
Me.[StartTime] = TimeValue(Now())
Omit the TimeValue() if you want the date as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:nf*********************@news20.bellglobal.com ...

I have a very simple db I use for keeping track of hours, tasks, projects,
clients etc. It has a form that I use to enter data. Currently the form has a textbox for a field called "start time", another for a field called "end
time" and a third for a field called "hours", among other controls. I now
type in the approximate times in the first two textboxes, and mentally
calculate and enter the difference or elapsed time in the third.

I have tried to automate the completion of this third field with no luck. I have tried entering an expression in the control source field of the text
box. I have tried stuff like "=[end time]-[start time]" and "=([end
time]-[start time]*24)" but I'm clearly lost. The first two textboxes are
formatted for "short time". "hours" is formatted as a "general number".

I would like to have the completion of the first two textboxes yield their
difference in the "hours" field in the underlying table, and display in the form textbox associated with this field. Some other considerations are:

Spanning midnight shouldn't be a problem.

I would like to round the hours to the nearest quarter, since I'm
approximating the time anyway.

I would appreciate any help anyone could extend. One more thing: is possible to get a control (button) to record the current time in a field?

[Larry Linson]

DateDiff is a built-in function to calculate date and time differences. The
Date/Time field or variable is for holding a "point in time" -- not a
"period of time", and trying to use it for the latter will, sooner or later,
lead to frustration and problems.

I suggest you use DateDiff to calculate the span of time in a unit of time
of your choosing, and store that in a numeric variable appropriate for the
time periods you are dealing with.

Larry Linson
Microsoft Access MVP
"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:nf*********************@news20.bellglobal.com ...

I have a very simple db I use for keeping track of hours, tasks, projects,
clients etc. It has a form that I use to enter data. Currently the form has a textbox for a field called "start time", another for a field called "end
time" and a third for a field called "hours", among other controls. I now
type in the approximate times in the first two textboxes, and mentally
calculate and enter the difference or elapsed time in the third.

I have tried to automate the completion of this third field with no luck. I have tried entering an expression in the control source field of the text
box. I have tried stuff like "=[end time]-[start time]" and "=([end
time]-[start time]*24)" but I'm clearly lost. The first two textboxes are
formatted for "short time". "hours" is formatted as a "general number".

I would like to have the completion of the first two textboxes yield their
difference in the "hours" field in the underlying table, and display in the form textbox associated with this field. Some other considerations are:

Spanning midnight shouldn't be a problem.

I would like to round the hours to the nearest quarter, since I'm
approximating the time anyway.

I would appreciate any help anyone could extend. One more thing: is possible to get a control (button) to record the current time in a field?
TIA
--

MichaelB
www.michaelbulatovich.com

[Michael Bulatovich]

Thanks for your response Allen. I'm an architect with no DB training so I'm
not sure what "data normalization" means. After thinking about it for a
second or two, I think you mean that there is a way that the three fields
could be made to not have the relationship of C=B-A, say when the form isn't
open, and that this could create a problem....
I currently have some queries that go after the "hours" field directly. What
I think you are saying is that a query is a place to do the 'end time minus
start time" work, not the table. I appreciate the advice.

As for the current time entry issue, what you suggest would enter the time
that the new record was made. Could it be done with a button, where you hit
the button and the current time is entered into a target field in the
record?

On the rounding issue, I don't know where/how the line "15 * Round([Minutes]
/ 15, 0)" would be used.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

For basic info on how to calculate the difference between two date/time
fields, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For rounding to the nearest 15 minutes, use:
15 * Round([Minutes] / 15, 0)

In your table, store the starting time and EITHER the ending time OR the
duration. Storing both violates a basic rule of data normalization. The
other one can be calculated in a query, as explained in the article above.

To pop the current time into a text box named "StartTime":
Me.[StartTime] = TimeValue(Now())
Omit the TimeValue() if you want the date as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:nf*********************@news20.bellglobal.com ...

I have a very simple db I use for keeping track of hours, tasks, projects, clients etc. It has a form that I use to enter data. Currently the form has

a textbox for a field called "start time", another for a field called "end time" and a third for a field called "hours", among other controls. I now type in the approximate times in the first two textboxes, and mentally
calculate and enter the difference or elapsed time in the third.

I have tried to automate the completion of this third field with no luck. I

have tried entering an expression in the control source field of the

text box. I have tried stuff like "=[end time]-[start time]" and "=([end
time]-[start time]*24)" but I'm clearly lost. The first two textboxes are formatted for "short time". "hours" is formatted as a "general number".

I would like to have the completion of the first two textboxes yield their difference in the "hours" field in the underlying table, and display in

the

form textbox associated with this field. Some other considerations are:

Spanning midnight shouldn't be a problem.

I would like to round the hours to the nearest quarter, since I'm
approximating the time anyway.

I would appreciate any help anyone could extend. One more thing: is

possible

to get a control (button) to record the current time in a field?

[Allen Browne]

Replies in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:Ha*********************@news20.bellglobal.com ...

Thanks for your response Allen. I'm an architect with no DB training so I'm not sure what "data normalization" means. After thinking about it for a
second or two, I think you mean that there is a way that the three fields
could be made to not have the relationship of C=B-A, say when the form isn't open, and that this could create a problem....
I currently have some queries that go after the "hours" field directly. What I think you are saying is that a query is a place to do the 'end time minus start time" work, not the table. I appreciate the advice.
Yes. The calcuation is done in the query, whenever you need it.
If it seems better to you to store the duration in minutes (as a Number
field) rather than the EndTime (as a date/time field), that's fine.
As for the current time entry issue, what you suggest would enter the time
that the new record was made. Could it be done with a button, where you hit the button and the current time is entered into a target field in the
record?
Yes: that line of code would go into the Event Procedure of for the Click
event of the command button.
On the rounding issue, I don't know where/how the line "15 * Round([Minutes] / 15, 0)" would be used.
That's up to you. It could be used in the query calculation, or it could be
used when storing the entry.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

For basic info on how to calculate the difference between two date/time
fields, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For rounding to the nearest 15 minutes, use:
15 * Round([Minutes] / 15, 0)

In your table, store the starting time and EITHER the ending time OR the
duration. Storing both violates a basic rule of data normalization. The
other one can be calculated in a query, as explained in the article above.

To pop the current time into a text box named "StartTime":
Me.[StartTime] = TimeValue(Now())
Omit the TimeValue() if you want the date as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:nf*********************@news20.bellglobal.com ...

I have a very simple db I use for keeping track of hours, tasks, projects, clients etc. It has a form that I use to enter data. Currently the form has

a textbox for a field called "start time", another for a field called "end time" and a third for a field called "hours", among other controls. I now type in the approximate times in the first two textboxes, and mentally
calculate and enter the difference or elapsed time in the third.

I have tried to automate the completion of this third field with no luck.

I

have tried entering an expression in the control source field of the text box. I have tried stuff like "=[end time]-[start time]" and "=([end
time]-[start time]*24)" but I'm clearly lost. The first two textboxes are formatted for "short time". "hours" is formatted as a "general

number".
I would like to have the completion of the first two textboxes yield

their difference in the "hours" field in the underlying table, and display in the

form textbox associated with this field. Some other considerations

are:
Spanning midnight shouldn't be a problem.

I would like to round the hours to the nearest quarter, since I'm
approximating the time anyway.

I would appreciate any help anyone could extend. One more thing: is

possible

to get a control (button) to record the current time in a field?

[Michael Bulatovich]

I've got it working! (This reminds me of when I started to learn LISP. It
was so painful, yet gratifying.)

I created two command buttons, on called btnStart and the other btnStop. The
onclick property for both invoke an "event procedure", which are both
basically the same function that you gave me :

Private Sub btnStart_Click()
Me.[start time] = TimeValue(Now())
End Sub
&
Private Sub btnStop_Click()
Me.[end time] = TimeValue(Now())
End Sub

They dump the current time into the appropriate fields in the form and
table. (YAY!) However they do so in the following format :

9:51:14 AM (AAAWW!)

despite the fact that the form textbox is formatted for "Short Time". I will
occasionally be entering time manually in these fields and the format is
more precise than I need. Can I format it for 'military time' and without
seconds somehow? Yesterday I got my queries to calculate the elapsed time
between the two fields when they were completed with my usual data like:

14:30
&
18:45

in the "start time" and "end time" fields, and put it in a field called
"extras". My reports take the "extras" and convert them from minutes to
fractional hours with:

=[extras]/60

in the control source property of a text box. I'm only guessing, but won't
the queries choke on " 9:51:14 AM " if they worked on " 18:45 "? Aren't they
different data types? In the query's field line I have:

Minutes: Sum(DateDiff("n",[start time],[end time]))

which I think you gave me more or less, and delivers time in minutes.

Thanks again for your generosity.
--

MichaelB
www.michaelbulatovich.com
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

Replies in-line.

"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:Ha*********************@news20.bellglobal.com ...

As for the current time entry issue, what you suggest would enter the time that the new record was made. Could it be done with a button, where you hit

the button and the current time is entered into a target field in the
record?

Yes: that line of code would go into the Event Procedure of for the Click
event of the command button.

On the rounding issue, I don't know where/how the line "15 *

Round([Minutes]

/ 15, 0)" would be used.

That's up to you. It could be used in the query calculation, or it could

be used when storing the entry.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

For basic info on how to calculate the difference between two date/time fields, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

For rounding to the nearest 15 minutes, use:
15 * Round([Minutes] / 15, 0)

To pop the current time into a text box named "StartTime":
Me.[StartTime] = TimeValue(Now())
Omit the TimeValue() if you want the date as well.

[Allen Browne]

To show only hours and minutes, set the Format property of the text box to:
hh:nn
(Note that you use n for miNutes, as m is for Month.)

If you want to use a 24-hour clock, open the Windows Control Panel, and go
to Regional Options.

Access stores a date/time value as a number where the integer part indicates
the date, and the fractional part the time of day (e.g. .5= noon, .25 =
6am). Regardless of whether the time is shown as 6:00:00 PM or 18:00, it
will still be stored as 0.75 internally, and the math will still work.

As the article pointed out, durations are better converted to a number of
minutes (or seconds or whatever).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:x0*********************@news20.bellglobal.com ...

I've got it working! (This reminds me of when I started to learn LISP. It
was so painful, yet gratifying.)

I created two command buttons, on called btnStart and the other btnStop. The onclick property for both invoke an "event procedure", which are both
basically the same function that you gave me :

Private Sub btnStart_Click()
Me.[start time] = TimeValue(Now())
End Sub
&
Private Sub btnStop_Click()
Me.[end time] = TimeValue(Now())
End Sub

They dump the current time into the appropriate fields in the form and
table. (YAY!) However they do so in the following format :

9:51:14 AM (AAAWW!)

despite the fact that the form textbox is formatted for "Short Time". I will occasionally be entering time manually in these fields and the format is
more precise than I need. Can I format it for 'military time' and without
seconds somehow? Yesterday I got my queries to calculate the elapsed time
between the two fields when they were completed with my usual data like:

14:30
&
18:45

in the "start time" and "end time" fields, and put it in a field called
"extras". My reports take the "extras" and convert them from minutes to
fractional hours with:

=[extras]/60

in the control source property of a text box. I'm only guessing, but won't
the queries choke on " 9:51:14 AM " if they worked on " 18:45 "? Aren't they different data types? In the query's field line I have:

Minutes: Sum(DateDiff("n",[start time],[end time]))

which I think you gave me more or less, and delivers time in minutes.

Thanks again for your generosity.
--

MichaelB
www.michaelbulatovich.com
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

Replies in-line.

"Michael Bulatovich" <Pl****@dont.try> wrote in message
news:Ha*********************@news20.bellglobal.com ...

As for the current time entry issue, what you suggest would enter the time that the new record was made. Could it be done with a button, where

you

hit

the button and the current time is entered into a target field in the
record?

Yes: that line of code would go into the Event Procedure of for the Click event of the command button.

On the rounding issue, I don't know where/how the line "15 *

Round([Minutes]

/ 15, 0)" would be used.

That's up to you. It could be used in the query calculation, or it could

be

used when storing the entry.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
> For basic info on how to calculate the difference between two

date/time > fields, see:
> Calculating elapsed time
> at:
> http://allenbrowne.com/casu-13.html
>
> For rounding to the nearest 15 minutes, use:
> 15 * Round([Minutes] / 15, 0)
>
> To pop the current time into a text box named "StartTime":
> Me.[StartTime] = TimeValue(Now())
> Omit the TimeValue() if you want the date as well.
>