syntax help for timer

  • Thread starter Thread starter bird lover
  • Start date Start date
B

bird lover

I used Access 2003 on xp professional. On form TIMESPENT I have four
fields., [start],[end],[summary],[running]. I want to calculate time elapsed
when working on a project. The control on the form for [summary] is
=HoursAndMinutes([END]-[START]). Usually I get interrupted with other
matters and have to come back.

My amateur solution" When I double click on [summary] or [running], I want
[running] to equal ([running] + [summary]. Then I want [start] AND [end] to
be nulled. This way if I get interrupted, [running] permanently shows time
elapsed. Then when I return to the record, I click on [start], finish with
[end] and I get the total time in hours and minutes for the two to four times
I return to the project.

I used Access template for time, but it doesn't provide for interruptions.

Suggested syntax would be greatly appreciated. My efforts with running are
unsuccessful.
 
bird said:
I used Access 2003 on xp professional. On form TIMESPENT I have four
fields., [start],[end],[summary],[running]. I want to calculate time elapsed
when working on a project. The control on the form for [summary] is
=HoursAndMinutes([END]-[START]). Usually I get interrupted with other
matters and have to come back.

My amateur solution" When I double click on [summary] or [running], I want
[running] to equal ([running] + [summary]. Then I want [start] AND [end] to
be nulled. This way if I get interrupted, [running] permanently shows time
elapsed. Then when I return to the record, I click on [start], finish with
[end] and I get the total time in hours and minutes for the two to four times
I return to the project.

I used Access template for time, but it doesn't provide for interruptions.

Suggested syntax would be greatly appreciated. My efforts with running are
unsuccessful.
Click to expand...

Struggled to get my head around this. Access is essentially a data
storage/retrieval system - a relational database - with forms, reports
and modules bolted on. In my experience the best (and quickest) results
come about when you concentrate first on what you are storing and how.

I guess I'd want to store a list of projects, and also a list of
workperiods associated with those projects: one project to many
workperiods. Two tables with a foreign key.

A convenient form might have a drop-down or list box to select the
relevant project, a subform showing workperiods already recorded for
that project, and Start and End buttons. One of these should be
disabled at any time, depending on the current status (if you're already
working on a project and haven't clicked End yet then you shouldn't be
able to click Start for that project, and if there is only one of you,
maybe you shouldn't be able to click Start if there is any project "in
progress".) You'd want a "Cancel" button as well, so that when you come
off that long phonecall and find the timer still running you can fix it.

When you click the Start button, the "Event" handler in Access (the
OnClick subroutine) would record the time in a control on the form, and
maybe change the visibility of a label so that the words "In Progress"
(or similar) show up prominently. It would also disable the Start
button. When you click the End button, the OnClick event handler would
record the time again, in a separate field (you can always calculate the
interval later if you store both times), save the record (unless you
want to add notes?), disable the End button, enable the Start button and
requery the form and subform so that the newly added workperiod shows up
in the subform, finally making the "In Progress" label invisible. Your
form could contain a text box which shows the total time booked to the
selected project so far, picking up the value from a hidden control
which sums the calculated intervals in a footer in the subform. Your
form would have as its Record Source a query which included the
difference between the times as a calculated field (look up the DateDiff
function for this).

So, in outline, your form provides a way of adding a record to the
database from clicking buttons, and also displays a summary of these
records in a subform. Any good?

None of this is that hard. Tackle one part at a time, but (as always)
figure out what you're going to store first.

HTH

Phil, London
 
I very much appreciate Mr. Herlihy's assistance, but much of it is over my
head. I'm simply requesting the correct syntax for the following:

[end]-[start] = [summary]
[end]=null
start=null
[running]=[running] + [summary]

I double click on [end] and the above code is in [end]. The system clears
out [start and [end] and I go back to the system after an interruption.

Philip Herlihy said:
bird said:
I used Access 2003 on xp professional. On form TIMESPENT I have four
fields., [start],[end],[summary],[running]. I want to calculate time elapsed
when working on a project. The control on the form for [summary] is
=HoursAndMinutes([END]-[START]). Usually I get interrupted with other
matters and have to come back.

My amateur solution" When I double click on [summary] or [running], I want
[running] to equal ([running] + [summary]. Then I want [start] AND [end] to
be nulled. This way if I get interrupted, [running] permanently shows time
elapsed. Then when I return to the record, I click on [start], finish with
[end] and I get the total time in hours and minutes for the two to four times
I return to the project.

I used Access template for time, but it doesn't provide for interruptions.

Suggested syntax would be greatly appreciated. My efforts with running are
unsuccessful.
Click to expand...

Struggled to get my head around this. Access is essentially a data
storage/retrieval system - a relational database - with forms, reports
and modules bolted on. In my experience the best (and quickest) results
come about when you concentrate first on what you are storing and how.

I guess I'd want to store a list of projects, and also a list of
workperiods associated with those projects: one project to many
workperiods. Two tables with a foreign key.

A convenient form might have a drop-down or list box to select the
relevant project, a subform showing workperiods already recorded for
that project, and Start and End buttons. One of these should be
disabled at any time, depending on the current status (if you're already
working on a project and haven't clicked End yet then you shouldn't be
able to click Start for that project, and if there is only one of you,
maybe you shouldn't be able to click Start if there is any project "in
progress".) You'd want a "Cancel" button as well, so that when you come
off that long phonecall and find the timer still running you can fix it.

When you click the Start button, the "Event" handler in Access (the
OnClick subroutine) would record the time in a control on the form, and
maybe change the visibility of a label so that the words "In Progress"
(or similar) show up prominently. It would also disable the Start
button. When you click the End button, the OnClick event handler would
record the time again, in a separate field (you can always calculate the
interval later if you store both times), save the record (unless you
want to add notes?), disable the End button, enable the Start button and
requery the form and subform so that the newly added workperiod shows up
in the subform, finally making the "In Progress" label invisible. Your
form could contain a text box which shows the total time booked to the
selected project so far, picking up the value from a hidden control
which sums the calculated intervals in a footer in the subform. Your
form would have as its Record Source a query which included the
difference between the times as a calculated field (look up the DateDiff
function for this).

So, in outline, your form provides a way of adding a record to the
database from clicking buttons, and also displays a summary of these
records in a subform. Any good?

None of this is that hard. Tackle one part at a time, but (as always)
figure out what you're going to store first.

HTH

Phil, London
Click to expand...
 
You lost me toward the end but I will give it a try...

On the AfterUpdate event of your field Summary put:
Me.Running = Me.Summary + Me.Running
Me.Start = ""
Me.End = ""

On the AfterUpdate event of Start and End put:
Me.Summary = DateDiff("n",[Start],[End])/60

This will give you the time spent as a number, if you want to get it into
the hours and minutes format, let us know.

Jackie
 
Philip's observations are right on point...

If you don't understand "relational" or "well-normalized", you will try to
make Access perform like a spreadsheet, which it isn't. You can pay now
(learn about the tool before proceeding) or pay later (keep coming up with
work-arounds because you don't understand the tool).

It all starts with the data...

Regards

Jeff Boyce
Microsoft Office/Access MVP


bird lover said:
I very much appreciate Mr. Herlihy's assistance, but much of it is over my
head. I'm simply requesting the correct syntax for the following:

[end]-[start] = [summary]
[end]=null
start=null
[running]=[running] + [summary]

I double click on [end] and the above code is in [end]. The system clears
out [start and [end] and I go back to the system after an interruption.

Philip Herlihy said:
bird said:
I used Access 2003 on xp professional. On form TIMESPENT I have four
fields., [start],[end],[summary],[running]. I want to calculate time
elapsed
when working on a project. The control on the form for [summary] is
=HoursAndMinutes([END]-[START]). Usually I get interrupted with other
matters and have to come back.

My amateur solution" When I double click on [summary] or [running], I
want
[running] to equal ([running] + [summary]. Then I want [start] AND
[end] to
be nulled. This way if I get interrupted, [running] permanently shows
time
elapsed. Then when I return to the record, I click on [start], finish
with
[end] and I get the total time in hours and minutes for the two to four
times
I return to the project.

I used Access template for time, but it doesn't provide for
interruptions.

Suggested syntax would be greatly appreciated. My efforts with running
are
unsuccessful.
Click to expand...

Struggled to get my head around this. Access is essentially a data
storage/retrieval system - a relational database - with forms, reports
and modules bolted on. In my experience the best (and quickest) results
come about when you concentrate first on what you are storing and how.

I guess I'd want to store a list of projects, and also a list of
workperiods associated with those projects: one project to many
workperiods. Two tables with a foreign key.

A convenient form might have a drop-down or list box to select the
relevant project, a subform showing workperiods already recorded for
that project, and Start and End buttons. One of these should be
disabled at any time, depending on the current status (if you're already
working on a project and haven't clicked End yet then you shouldn't be
able to click Start for that project, and if there is only one of you,
maybe you shouldn't be able to click Start if there is any project "in
progress".) You'd want a "Cancel" button as well, so that when you come
off that long phonecall and find the timer still running you can fix it.

When you click the Start button, the "Event" handler in Access (the
OnClick subroutine) would record the time in a control on the form, and
maybe change the visibility of a label so that the words "In Progress"
(or similar) show up prominently. It would also disable the Start
button. When you click the End button, the OnClick event handler would
record the time again, in a separate field (you can always calculate the
interval later if you store both times), save the record (unless you
want to add notes?), disable the End button, enable the Start button and
requery the form and subform so that the newly added workperiod shows up
in the subform, finally making the "In Progress" label invisible. Your
form could contain a text box which shows the total time booked to the
selected project so far, picking up the value from a hidden control
which sums the calculated intervals in a footer in the subform. Your
form would have as its Record Source a query which included the
difference between the times as a calculated field (look up the DateDiff
function for this).

So, in outline, your form provides a way of adding a record to the
database from clicking buttons, and also displays a summary of these
records in a subform. Any good?

None of this is that hard. Tackle one part at a time, but (as always)
figure out what you're going to store first.

HTH

Phil, London
Click to expand...
Click to expand...
 
Dear Jackie:

I think I'm better off hiring a part time consultant. If you are available,
please email me at (e-mail address removed), or call me during New York
business hours at 516 223-8000 x. 205

Jackie L said:
You lost me toward the end but I will give it a try...

On the AfterUpdate event of your field Summary put:
Me.Running = Me.Summary + Me.Running
Me.Start = ""
Me.End = ""

On the AfterUpdate event of Start and End put:
Me.Summary = DateDiff("n",[Start],[End])/60

This will give you the time spent as a number, if you want to get it into
the hours and minutes format, let us know.

Jackie

bird lover said:
I used Access 2003 on xp professional. On form TIMESPENT I have four
fields., [start],[end],[summary],[running]. I want to calculate time elapsed
when working on a project. The control on the form for [summary] is
=HoursAndMinutes([END]-[START]). Usually I get interrupted with other
matters and have to come back.

My amateur solution" When I double click on [summary] or [running], I want
[running] to equal ([running] + [summary]. Then I want [start] AND [end] to
be nulled. This way if I get interrupted, [running] permanently shows time
elapsed. Then when I return to the record, I click on [start], finish with
[end] and I get the total time in hours and minutes for the two to four times
I return to the project.

I used Access template for time, but it doesn't provide for interruptions.

Suggested syntax would be greatly appreciated. My efforts with running are
unsuccessful.
Click to expand...
Click to expand...
 
bird lover said:
I very much appreciate Mr. Herlihy's assistance, but much of it is over my
head. I'm simply requesting the correct syntax for the following:

[end]-[start] = [summary]
[end]=null
start=null
[running]=[running] + [summary]

I double click on [end] and the above code is in [end]. The system clears
out [start and [end] and I go back to the system after an interruption.
Click to expand...

At first glance I'd say that you need to calculate that in a query but I do
agree with other posters that you seem to be thinking in spreadsheet mode
(no offence intended).

Keith.
www.keithwilby.co.uk
 
Back
Top