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 |
#11
|
|||
|
|||
Access 2007 + Time Formats - Sports Clocking
"flebber" wrote: On Mar 16, 11:56 am, "a a r o n . k e m p f @ g m a i l . c o m" wrote: I'm not sure that Access datatypes have the level of precision that you're looking for On Mar 14, 7:03 pm, Sayth wrote: I have a database I am starting to create. I need some guidance in how to best create a time format for a "sports watch". What I need to record is minutes:seconds:hundredths, however the times will need be manually inputted or imported. There will also be basic calculations performed on times e.g car a time for lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times and then be able to calculate differences in time and store them. There seems to be two basic ways to go 1) create an input mask and create a text field as mm:ss:uu, only I am unsure how to validate the inputs so that seconds greater than 60 cannot be created or hundredths greater than 99.. Seems may have calculations complications to. Create a table/template/format so that each time is inputted into separate fields, so that minutes has its own field seconds etc. Then have a field where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill level but makes more sense, would like more information on this approach if it is better? Any ideas? Other ideas besides my two above appreciated. Maybe not, but Access has many other advantages. The fact that a "duration" data-type doesn't exist is strange to me as a newbie since a wide range of sports use them, cooking and recipes rely on duration though maybe not as exacting, and there are plenty of business uses, call centres (handle times/durations) etc. But testing the solutions I am learning and should be able to overcome the time/duration limitation. . Found this article on SQL, most of the content seems relevant to Access as well. Interesting that SQL has no data-type for durations as well. This is a very good article and very much reinforces your method John. http://www.sqlteam.com/article/worki...-in-sql-server |
#12
|
|||
|
|||
Access 2007 + Time Formats - Sports Clocking
"Sayth" wrote: "John W. Vinson" wrote: On Mon, 15 Mar 2010 04:54:35 -0700 (PDT), flebber wrote: Thank you am trying to follow but I am not yet as advanced as you. So the private sub names and fields change with each sub section so seconds is Private Sub txtSeconds_AfterUpdate() Me!txtRacetime = NZ(Me!txMinutes)*60 + NZ(Me!txtSaeconds) * 60 _ + NZ(Me!txtSeconds) End Sub That leaves me then with the input from I believe 2 unbound form boxes for me being "minutes" and "seconds" and hundredths of seconds recorded in a currency field. Am I then amalgamating the fields to one field or how will I perform calculations between time? ([txtMinutes]+[txtSeconds]+[hundredths]) + or - a similar combined number? I'm suggesting storing the seconds and hundredths in one number - I don't see any benefit to entering 13 (seconds) in one textbox and 41 (hundredths) in a different textbox, vs. typing 13.41 into a single textbox. The expression I posted: Private Sub txtMinutes_AfterUpdate() Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _ + NZ(Me!txtSeconds) End Sub generates the combined number. The NZ (Null To Zero) function prevents errors when a textbox is empty: anything plus NULL gives NULL, and the NZ function converts the null value into a zero. There are 3600 seconds in an hour, and 60 in a minute, so I'm multiplying the entered number of hours by 3600 and the entered number of minutes by 60, and adding those two products to the number of seconds in the third textbox. What I'm suggesting is that you have this identical expression in the AfterUpdate event of the three textboxes, which I named txtHours, txtMinutes, and txtSeconds. If you change any one of them, it will recalculate the sum of the three and store the result into the textbox named txtRacetime, which would be bound to the (currency, or decimal, or Double) race time field in your table. It might be better to explicitly convert the calculated result to your desired datatype: e.g. if the race time is a Currency field, use Private Sub txtMinutes_AfterUpdate() Me!txtRacetime = CCur(NZ(Me!txtHours)*3600) _ + CCur(NZ(Me!txtMinutes) * 60) _ + CCur(NZ(Me!txtSeconds)) End Sub You would also want to do the reverse - display the hours, minutes, and seconds when you navigate to a record which already has a time. To do so put code in the form's Current event: Private Sub Form_Current() If Not IsNull(Me!txtRacetime) Then Me!txtHours = Me!txtRacetime \ 3600 Me!txtMinutes = Me!txtRaceTime \ 60 MOD 60 Me!txtSeconds = Me!txtRaceTime - 60 * (Me!txtRacetime \ 60) End If End Sub The \ isn't a typo - it's the integer divide operator, so a a racetime of 128.24 seconds, integer divided by 60, is 2 minutes. The last expression gets the remaining 8.24 seconds. -- John W. Vinson [MVP] . The penny finally dropped as to how exactly this was working. Got it. Awesome thanks Heaps John for your help. Your time and effort very much appreciated. Above I found two articles that reinforce what you have already advised but they are very helpful as well, they are SQL artciles but seem quite relevant to Access. http://weblogs.sqlteam.com/jeffs/arc.../02/56079.aspx http://www.sqlteam.com/article/worki...-in-sql-server |
#13
|
|||
|
|||
Access 2007 + Time Formats - Sports Clocking
http://www.sqlteam.com/article/worki...-in-sql-server
But note that people DO work with months and years :~) Rent and Financial Instruments are two areas that spring to mind, so some people need to and do calculate duration in months and years. (david) "Sayth" wrote in message ... "Sayth" wrote: "John W. Vinson" wrote: On Mon, 15 Mar 2010 04:54:35 -0700 (PDT), flebber wrote: Thank you am trying to follow but I am not yet as advanced as you. So the private sub names and fields change with each sub section so seconds is Private Sub txtSeconds_AfterUpdate() Me!txtRacetime = NZ(Me!txMinutes)*60 + NZ(Me!txtSaeconds) * 60 _ + NZ(Me!txtSeconds) End Sub That leaves me then with the input from I believe 2 unbound form boxes for me being "minutes" and "seconds" and hundredths of seconds recorded in a currency field. Am I then amalgamating the fields to one field or how will I perform calculations between time? ([txtMinutes]+[txtSeconds]+[hundredths]) + or - a similar combined number? I'm suggesting storing the seconds and hundredths in one number - I don't see any benefit to entering 13 (seconds) in one textbox and 41 (hundredths) in a different textbox, vs. typing 13.41 into a single textbox. The expression I posted: Private Sub txtMinutes_AfterUpdate() Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _ + NZ(Me!txtSeconds) End Sub generates the combined number. The NZ (Null To Zero) function prevents errors when a textbox is empty: anything plus NULL gives NULL, and the NZ function converts the null value into a zero. There are 3600 seconds in an hour, and 60 in a minute, so I'm multiplying the entered number of hours by 3600 and the entered number of minutes by 60, and adding those two products to the number of seconds in the third textbox. What I'm suggesting is that you have this identical expression in the AfterUpdate event of the three textboxes, which I named txtHours, txtMinutes, and txtSeconds. If you change any one of them, it will recalculate the sum of the three and store the result into the textbox named txtRacetime, which would be bound to the (currency, or decimal, or Double) race time field in your table. It might be better to explicitly convert the calculated result to your desired datatype: e.g. if the race time is a Currency field, use Private Sub txtMinutes_AfterUpdate() Me!txtRacetime = CCur(NZ(Me!txtHours)*3600) _ + CCur(NZ(Me!txtMinutes) * 60) _ + CCur(NZ(Me!txtSeconds)) End Sub You would also want to do the reverse - display the hours, minutes, and seconds when you navigate to a record which already has a time. To do so put code in the form's Current event: Private Sub Form_Current() If Not IsNull(Me!txtRacetime) Then Me!txtHours = Me!txtRacetime \ 3600 Me!txtMinutes = Me!txtRaceTime \ 60 MOD 60 Me!txtSeconds = Me!txtRaceTime - 60 * (Me!txtRacetime \ 60) End If End Sub The \ isn't a typo - it's the integer divide operator, so a a racetime of 128.24 seconds, integer divided by 60, is 2 minutes. The last expression gets the remaining 8.24 seconds. -- John W. Vinson [MVP] . The penny finally dropped as to how exactly this was working. Got it. Awesome thanks Heaps John for your help. Your time and effort very much appreciated. Above I found two articles that reinforce what you have already advised but they are very helpful as well, they are SQL artciles but seem quite relevant to Access. http://weblogs.sqlteam.com/jeffs/arc.../02/56079.aspx http://www.sqlteam.com/article/worki...-in-sql-server |
|
Thread Tools | |
Display Modes | |
|
|