Help with formatting field to show duration not time (1 Viewer)

[Kelly]

Hello everyone

Need help and I cannot find the answer anywhere yet I am sure this is a common requirement for a lot databases.

I am building a database to enter staff phone statistics. As an example my fields would be - Name, Date, Staffed time, Available time, Aux time and then calculated fields to show the percentage of time i.e %Aux, %Available etc.

My problem is the formatting of the times entered as they are duration not time. Say staffed time is entered as 08:00:00 for 8 hours and Aux time 03:57:21. The only format I can see to suit is date time but then Access takes these entries as 8am and 3:57am is there a way to change this to work as duration hh:mm:ss? I am becoming very frustrated with this.

Thank you in advance

PS i have very basic knowledge of Access

 

[jdraw]

see this for related info

TechontheNet has various Access functions with examples.

 

[Kelly]

Thanks I have looked at DateAdd before and cannot see a way it can relate to duration.

 

[CanadianAccessUser]

My db does the exact same thing including a full scorecard.
My solution to this problem was a function that puts the time into hh:mm:ss format. I'll post the code below for you to create your own module with, once you have the module you can use it in your reports AFTER YOU AVERAGE if averaging. With this solution I can simply use subtraction to get the duration in seconds and format it in the report.

This code only works if you are getting your telephony stats in seconds:

Function getMinuteFormat(ByVal in_Seconds As Long) As String
 
    getMinuteFormat = in_Seconds \ 3600 & ":" _
                    & Format(Fix((in_Seconds Mod 3600) \ 60), "00") & ":" _
                    & Format(in_Seconds Mod 60, "00")
 
End Function

Good luck!

 

[SmallTomato]

I have looked around the internet for this issue and it looks like most people approach it the way CanadianAccessUser does. They store it in total minutes or seconds and then convert it when needed.

 

[Kelly]

Thanks CanadianAccessUser. I'm a complete novice though so I am unsure exactly about your code. I have tried to make queries where I have changed the hh:nn:ss to seconds using an expression this seems to work, however I'm not really sure yet, my problem is a query to work out the percentage the one I have made gives me the correct percentage for the first line but then averages all new entries. I'm missing something.

if you have time could you explain your code to me.

Thanks

 

[CanadianAccessUser]

Ok Kelly, I've been there for sure.

1. Create a new module.
2. Paste that code in it. (Courtesy of help on this site)
3. Create all your queries with the data in seconds. (Do not average)
4. Create your reports.
5. In the text boxes in your report you're going to do something like:
=getMinuteFormat([StaffTime])
In your footers for averages or sums put something like:
=getMinuteFormat(Avg([StaffTime]))
6. If you want it broken down by team, create group headers and footers for supervisors. Put the supervisor's name field in the header, team total in the footer.
7. Overall client total goes in the report footer with the above expression.

It is important that you:
A. Average before formatting to hh:mm:ss
and
B. Be very careful you don't average an average accidentally. It's very easy to do with your Average Handling Time. You may even have to break it down to just handling time in order to ensure accuracy, but that depends on what data you're working with.