Access Book collection Database

[sam]

Hi i have downloaded the book collection database from the microsoft website,

I have converted this database to keep a database of my audiobook collection
this works very well but i have one problem to work out yet, and any help
would be great.

In the database i have a field for the playing time of each audiobook some
are quite a few hours long and i would like access to add up the playing time
in any report i print.

I can get access to print a total number of books but i am unable to get it
to add all the playing times and give a total number og hours.

Is this possible please

Regared to you all

 

[Ken Sheridan]

Its important when dealing with date/time data to understand just how these
type of values are implemented in Access.

Firstly there is no such thing as a time value per se, or a date value per
se. Because day-zero in Access is 30 December 1899 when you enter a time
into a date/time field you are in fact entering the time on that day rather
than a duration of time. Conversely when you enter a date you are entering
the point of time at midnight at the start of the day in question.

While you see date/time values formatted as such, Access actually implements
them as 64 bit floating point numbers as an offset from 30 December 1899
00:00:00 with the integer part representing the days and the fractional part
the times of day. So when you add time values together you are really adding
the underlying floating point numbers which represent those values. The
result will be the sum of these numbers, and if you then format this as a
date/time value it will show the date/time which the number represents. If
the sum of the values is less than 24 hours this will be fine, but above that
you'll see a date and time rather than the total time duration.

It is possible to show the sum of time values as the total hours, minutes
and seconds, however, by using some code to convert the underlying number to
a string. First paste the following function into a standard module in your
database:

Public Function TimeSum(dblTotalTime As Double) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

TimeSum = lngHours & strMinutesSeconds

End Function

If the module is a new one rather than an existing one save it with a
different name from the function, e.g. basDateTimeStuff.

You can call the function in a column in the query, e.g.

SELECT COUNT(*) AS [Number of Books],
TIMESUM(SUM([Playing Time])) AS [Total Playing Time]
FROM [Your Table];

A form or report could be based on the query. Or you could call the
function in an expression as the ControlSource of a text box on a form or
report, using the built in DSum function to sum the times and the above
function to return the value as a string in the format h:nn:ss:

=TimeSum(DSum("[Playing Time]","[Your Table]))

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

PS: you could of course also call the function in the ControlSource of a
text box in a group or report footer in a report:

=TimeSum(Sum([Playing Time]))

Ken Sheridan
Stafford, England

 

[sam]

Thanks for the information i will have a go at your surgestion. all i need to
do is input the amount of time a audiobook plays for and then when i print a
report it adds up the total amount of playing time for all the book in the
report

Thanks again