Dividing Time from code in a query

[jw5107]

Below is code I have retrieved from peterssoftware.com.
Custom Time Funciton.mdb - I got an example of how to use this in query:
Select ctf_Construct(ctf_Divide(ctf_Parse(MyTimeString), 3)) From MyTable;
But I can't get it to work. I have been messing with:
FltHrsDay: ctf_Construct("SELECT(ctf_Divide(ctf_Parse([TotHrsDay]),([TotACDays]))) FROM FleetUtilization")
But can't get it to work either. I get a data mismatch message. Any fixes???

Thanks in advance - jw

Function ctf_Construct(ct As cTime_struct) As String
'* convert a ctime_struct variable into a time string with subseconds
Dim s As String
Dim sSubSecondFormat As String
Dim lng As Long

lng = CLng(SubSecondsPerSecond) - 1
Select Case Len(CStr(lng))
Case 1
sSubSecondFormat = "0"
Case 3
sSubSecondFormat = "000"
Case 4
sSubSecondFormat = "0000"
Case 5
sSubSecondFormat = "00000"
Case 6
sSubSecondFormat = "000000"
Case Else
sSubSecondFormat = "00"
End Select

s = Format(ct.hour, "00") & ":" & Format(ct.minute, "00") & ":" & _
Format(ct.second, "00") & "," & Format(ct.subsecond, sSubSecondFormat)
ctf_Construct = s
End Function

Function ctf_Divide(ct As cTime_struct, intOperand As Integer) As cTime_struct
'* divide "ctime" by an integer
Dim minusct As cTime_struct
Dim ctres As cTime_struct
Dim inttemp As Integer
Dim remainder As Integer

'MsgBox ctime & " / " & intOperand
If intOperand <> 0 Then
ctres.hour = ct.hour \ intOperand
inttemp = (MinutesPerHour * (ct.hour Mod intOperand) + ct.minute)
ctres.minute = inttemp \ intOperand
inttemp = (SecondsPerMinute * (inttemp Mod intOperand) + ct.second)
ctres.second = inttemp \ intOperand
inttemp = (SubSecondsPerSecond * (inttemp Mod intOperand) + ct.subsecond)
ctres.subsecond = inttemp \ intOperand
Else
ctres.hour = 0
ctres.minute = 0
ctres.second = 0
ctres.subsecond = 0
End If
'MsgBox "result: " & ctres.hour & ":" & ctres.minute & ":" & ctres.second & "," & ctres.subsecond
'MsgBox ctf_Construct(ctsum)
ctf_Divide = ctres
End Function

Function ctf_Parse(ctime As String) As cTime_struct
'* break down the ctime string into an ctime structure
Dim ct As cTime_struct
Dim pos As Integer
Dim startpos As Integer
Dim SearchChar As String

startpos = 1
SearchChar = ":"
pos = InStr(ctime, SearchChar)
ct.hour = Val(Mid(ctime, startpos, pos - startpos))
'MsgBox ct.hour & " " & ct.minute & " " & ct.second & " " & ct.frame
startpos = pos + 1
pos = InStr(startpos, ctime, SearchChar)
ct.minute = Val(Mid(ctime, startpos, pos - startpos))
'MsgBox ct.hour & " " & ct.minute & " " & ct.second & " " & ct.frame
startpos = pos + 1
SearchChar = ","
pos = InStr(startpos, ctime, SearchChar)
ct.second = Val(Mid(ctime, startpos, pos - startpos))
'MsgBox ct.hour & " " & ct.minute & " " & ct.second & " " & ct.frame
startpos = pos + 1
ct.subsecond = Val(Mid(ctime, startpos, Len(ctime) + 1 - startpos))
'MsgBox ct.hour & " " & ct.minute & " " & ct.second & " " & ct.frame
ctf_Parse = ct
End Function

 

[jw5107]

PHV,

Yes I have thank you. However - it still don't work. I have tried changing the data types around and still no luck.

Bummer!!

 

[PHV]

it still don't work
Please, be more specific !
Any error message ? Unexpected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

jw,

Michael's decimal is 2 places to the left.

But here's a formula since your "TIME" is a string...
[tt]
=(LEFT(TotHrsDay,FIND(":",TotHrsDay)-1)+RIGHT(TotHrsDay,LEN(TotHrsDay)-FIND(":",TotHrsDay)/24/TotAcDays
[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[jw5107]

PHV -
Access just literally shuts down. I get the message that wants to create a backup file.
I don't know why I can't get this to work in a query!!

Skip,
I get an error message about FIND in the expression??

Thanks for all your help!!

 

[SkipVought]

Then use InStr(TotHrsDay,":")

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[jw5107]

Skip,

these are the results:
Fleet TotHrsDay TotACDays Expr1
727 37:46 234 3737:46
747 122:39 245 122122:39
757 334:25 1517 334334:25
767 318:51 825 318318:51
A300 166:06 857 166166:06
DC-8 83:12 614 8383:12
MD-11 135:41 232 135135:41

Using:
Expr1: (Left([TotHrsDay],InStr([TotHrsDay],":")-1)+Right([TotHrsDay],Len([TotHrsDay])-InStr([TotHrsDay],":")/24/[TotAcDays]))

Any suggestions as to what I am doing wrong?? It looks like it is just copying and pasting the hrs or something??

 

[MichaelRed]

I recall some drivel re giving a man a fish ve teaching him how to catch his own. perhaps this is the wrong venue for such mussings. My 'sense' of this is one of (force) feeding pablum to the unruly child. Surely others will have more patience than this senior citizen.

Just for complete clarity:

? format(8.05258784425451E-02, "hh:nn:ss")
01:55:57

I shan't, however, EVER intrude on you again.

MichaelRed

 

[SkipVought]

oops,

I missed the scientific notation, Michael. Of course, you were right on!

I started on this one early on and got tired of it. [YAWN]

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[jw5107]

Man you guys are brutal!!!!
jw.

 

[jw5107]

MichaelRed,

Will your formula support results over 24hrs?
Say a result like 1645:26:00

 

[SkipVought]

Micheal's works PERFECTLY!

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[jw5107]

This is what I am getting from Michael's formula:

Fleet TotFleetHrs TotACDays MyhrsperDay
757 1053735:18 1517 28.9424110085695
Answer:
test
22:37:04

The answer should be 694:37:04

what am I missing??? Can his formula calculate over 24hrs, and if so - how do I apply it??

Thanks again,
jw

 

[SkipVought]

EXACTLY!!!

28.9424110085695 DAYS formatted as TIME is

694:37:04!!!


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[jw5107]

Using this:

Test: Format$([UsesperFltHrs],"hh:nn:ss")
I get 22:37:04

Not 694:37:04.

How do I do the formatting??

Thanks again,
jw

 

[PHV]

Try this:
Test: (24*Int(UsesperFltHrs)+Format(UsesperFltHrs),'h')) & Format(UsesperFltHrs),':nn:ss')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244