If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
syntax help for timer
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. |
#2
|
|||
|
|||
syntax help for timer
bird lover wrote:
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. 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 |
#3
|
|||
|
|||
syntax help for timer
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" wrote: bird lover wrote: 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. 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 |
#4
|
|||
|
|||
syntax help for timer
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" wrote: 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. |
#5
|
|||
|
|||
syntax help for timer
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" wrote in message ... 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" wrote: bird lover wrote: 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. 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 |
#6
|
|||
|
|||
syntax help for timer
|
#7
|
|||
|
|||
syntax help for timer
"bird lover" wrote in message
... 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. 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 |
Thread Tools | |
Display Modes | |
|
|