Any Access wiz's about?

[BliarOut]

I need a bit of help....

I'm trying to write a quick access database. Yes, I know Excel would be easier but it's been asked for in Access! Anyway, what I want to do is calculate the cost of a phone call retrieved from a .csv file. All well and good, but the call duration is in a decimal format, not number of seconds, ie 00:01:34. I can't manipulate the .csv prior to using it, it arrives as is.

It will be billing on a per second basis so how do I sum up call costs? If the cost is 20P per minute then that's .3333333p recurring per second. But how do I sum 00:01:34 X .33333p and come to 31.33p on a report?

[roadsweeper]

Hi

It's been a few years since I've done anything in Access and VBA but I think what you need to do is to extract the three values from the hh:mm:ss string using a simple VBA function.

I recall there is a function that allows you to split (or 'tokenise') a string, but I can't remember it, so the slightly less elegant alternative would be to split the string using the right, left and mid statements. For example

Dim strDuration as String
Dim bytHours as Byte
Dim bytMinutes as Byte
Dim bytSeconds as Byte
Dim intDuration as Integer

'Set strDuration equal to the duration string, e.g. 01:23:44.

bytHours = val(left$(strDuration, 2)
bytMinutes = val(right$(left$(strDuration, 5), 2)
bytSeconds = val(right$(strDuration, 2)

'Then convert the lot to seconds.

intDuration = (bytHours * 60 * 60) + (bytMinutes * 60) + bytSeconds

'Then the cost calculation for the call is trivial.

I hope this helps, but please bear in mind the syntax is probably awful as it's been a while! However, it should very strongly point you in the right direction.

[BliarOut]

Thanks.... That just gave me enough of a clue to find this:

Function TimeToMin(MyTime)
TimeToMin = CInt(CDbl(MyTime) * 24 * 60 * 60)
End Function

No idea what it does, but I modified it a bit by adding a *60 and it seems to work

[TheExcession]

BliarOut said:

No idea what it does, but I modified it a bit by adding a *60 and it seems to work

Was it when someone said something like this that it prompted the invention of unit testing?

[BliarOut]

TheExcession said:

BliarOut said: No idea what it does, but I modified it a bit by adding a *60 and it seems to work Was it when someone said something like this that it prompted the invention of unit testing?

Is this where we discuss Indian coding?

In my defence it's only a way for a colleague to work out which of his calls are work related and which are personal from a T-Mobile online bill and then charge the company.

[Alex]

Try this:

Function TimeInSeconds(myTime As String) As Integer
TimeInSeconds = (Hour(myTime) * 24 * 60) + (Minute(myTime) * 60) + (Second(myTime))
End Function


>> Edited by Alex on Monday 8th May 14:41

[orgasmicliving!!]

If you get the file as .csv, you can still use Excel to manipulate the file first, save the results as a .csv file again, and then import it into Access.

In Excel, you would use the string functions to create separate formulae for the hours, minutes and seconds to convert each to seconds. Next, you would add them all up and multiply by the per second cost. Finally, you would need to output the results to a .csv file with either the same name or a different one if you want to preserve the original file(s).

Or something like that.

[annodomini2]

Result = (Value * 20)/60