Help Calculating Total Length of Time on a Form

[RacerGirl117]

You guys'll like this...

I am working on a database to keep track of songs for my wedding. I have a table, tblSongs, that has a "Lenth" field in it that shows the length of the song. On the form I've created, based on a query, I want to calculate the total time of all songs in the form footer. I have an unbound control, TotalLength, where I've tried the obvious =sum([Length]), but that does not work. It puts the time as a whole number (i.e. 1429931). The field in the table does not have any formatting (other than Number and Long Integer) except for an input mask (99:00:00). Even if I put the same input mask in the unbound field on the form, it still shows the whole number.

How can I get this field to calculate propery?

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[Steve101]

Jessica,

Change the type of your "lenth" field in the Song table to DateTime, and set the format property to "hh:nn:ss" (without the quotes). Then, make sure that the entry for the length of every song is in hour, minutes, seconds format, For example, 0:3:15

The hours position will presumably always be 0, but I think that the hh: bit still needs to be there.

You should find then that the =Sum(Lenth) control, placed in the report footer works OK; just make sure that it too has the format of "hh:nn:ss".

BTW, you might be better off to create a new field first in the table, as converting the data type for Integer to DateType mightdo funny things to your existing data.

Hope this helps, and good luck for the wedding,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[RacerGirl117]

Steve,

What if I want to track down to the nano-second? i.e. 4 minutes, 13 seconds, and .07 of a second? Is there a "format" for that section of time? It's not THAT important, but you know how us geeky types are...we like to track as much as possible. lol

Also, if I put an input mask in there, would the field retain the formatting?

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[Steve101]

Jessica,

Far as I know, datetime format does'nt support fractional seconds.

You should be able to use an input mask, though I am not able to test it, as the mask wizard isnt installed on my computer, and I dont remember the syntax.

Now get out there and choose the dress, flowers, photographer, menu etc. and dont worry about the nanoseconds



Have fun,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[RacerGirl117]

haha Did all that already. Thanks Steve. I got it. I'm just going to round the numbers. When you download songs and record them to CD it gives you the nano seconds. Like I said, not that important. I just want to make sure I have enough music.

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com