I Need help with Call length fields (time)

[Nymandus]

I finally figured out how to make it possible to enter the length of time for a call (by changing the data type to text and putting a mask of 00:00:00 on it)... now my problem is that I can't figure out how to run my queries and reports with that information.

This is for a call center and the length of call is already calculated for us in another system so all we need is to enter that into the database.

calls will probably never be more than 15 min in length but I wanted to have the "off chance" ability in the database.
I want to be able to run reports to guage performance based on call lengths.

when I run my queries with the criteria searching for calls greater than 7 minutes long >"00:06:59"
I get all calls regardless of length...
When I run it for calls less than 7 minutes long <"00:07:00"
I get a return without any results

I've tried adding # in front and after the number >"#00:06:59#" and return the same results


I have the input mask on the Query as well.

any thoughts?

I am teaching myself access so unfortunetly I need lots of help and any you are able to provide will be greatly appreciated.

Michael

 

[Uncle Gizmo]

It looks to me like you are trying to do greater and lesser operations against a text string so you will not get the result you expect.

You will need to convert the text into a numeretic value and then run your queries against that.

 

[Nymandus]

Thank you Gizmo,

That makes sense. I had to use text to allow for call lengths greater than 23 min and 59 sec. Is there a simple method to convert the text time to numeric time in access.

do I make the change in the query?

how do I get access to recognize i am using numbers?

 

[Nymandus]

ok I figured out calculated fields in Query to get sperate hour minute and second fields.. I also created one that combines the fields but now it no longer looks like a time just string of numbers with out the colon. do I leave it like that and do somethign in the reports or is there something I can change in my calculated field to fix it?
below is what i am using

HMS: Mid$([Call Length],1,2) & Mid$([Call Length],3,2) & Mid$([Call Length],5,2)
For 45 hours, 21 minutes and 21 seconds it returns 452121

 

[gblack]

Hmm... seems jumbled up doing it like that... could you just concatenate a date string to the time string your created... and then convert the whole thing into a date... using the CDate() function?

For example: use the date function (i.e. Date()) and the value you created and concatenate them... like so:

CDate(Date() & "00:06:59 " & "PM")

That should give you something like this: #05/31/09 11:07:12 PM#

Then you could used the date functions on that to get your time values (Like dateadd, datediff etc...)

 

[rzw0wr]

I would use a date type.
Now() gives the date and time.
Make the hours and minutes a calculation in a query.
Call Stop - Call start = Hours:minutes:Seconds.

Dale