Adding Times

[RichMaine]

I have an Access database that tracks my Music Collection. The time for each track is a Date/Time Field formatted to show only the time portion. I created a report in Crystal that groups songs by album. I tried to create a summary field that calculates the length of the album, but the sum function is not avaailable for the Date/Time field that holds the length of each track. Is it possible to convert the Dat/Time field to a numeric so that it can be added?

 

[Reebo99]

I'm assuming you've already got a group for the individual CD.

Create a formula to calculate the number of seconds:

DateDiff("s",DateTime(CurrentDate,Time(0,0,0)),DateTime(CurrentDate,Time({CD_Times.Duration})))

Replace Item in bold with your DateTime field.

Then Sum this field per group. Then calculate the total time :

Time(DateAdd("s",Sum ({@Difference}, {CD_Times.Title}),DateTime(CurrentDate,Time(0,0,0))))

Replace the item in bold with your real SUM details.

Hope this helps...

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[RichMaine]

I must have done something wrong, but I can't figure out what it is. Here is what I did and an example of the results.

I created two formulas (one called Difference, and one called TotalTimes)The first one holds the first formula that you posted, and the second refererences the first but adds a runnig total for the group.

When ther is only one song (total is less than 10 minutes), it works fine.

Assorted Ozzy
Shot In The Dark 4:16
Total Time 4:16

The problem comes when the total is greater than 10

Assorted Marshall Tucker
Heard it In a Love Song 4:57
Ramblin' 5:07
Running Like the Wind 9:10
Fly Like An Eagle 3:02
This Ol Cowboy 6:47
Losing You 5:09
Long Hard Ride 3:51
Searchin For a Rainbow 3:32
Total Time 17:35

I don't know where the mistake could be I double and triple checked the formulas.

This is the formula for Difference:
DateDiff("s",DateTime(CurrentDate,Time(0,0,0)),DateTime(CurrentDate,Time({Tracks.TrackLength})))

This is the formula for TotalTime:
Time(DateAdd("s",Sum({@Difference},{Recording_with_Album_Covers.RecordingID}),DateTime(CurrentDate,Time(0,0,0)))).

 

[RichMaine]

I solved the problem. I queried the Date/Time field. The minutes were stored as hours, and the seconds as minutes. I added up the hours (minutes) and the minutes (seconds) for the group. Then using the mod function (total minutes (seconds) mod 60), I obtained the number of seconds that were "left over". I used the Truncate function and divided the total number of minutes (seconds) by 60 to get the hours (minutes to be added to the total hours (minutes). I then used the CStr function to return the string representation of both the hours (minutes) and minutes (seconds) and concatenated the results.

I'm sure this is clear as mud, and there probably is a simpler way to do it, but it does work, and for that I am happy.