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

 

[hneal98]

This was your original example:

Select ctf_Construct(ctf_Divide(ctf_Parse(MyTimeString), 3)) From MyTable;

Here is what you are trying to do:

ctf_Construct("SELECT(ctf_Divide(ctf_Parse([TotHrsDay]),([TotACDays]))) FROM FleetUtilization")

Note in the original example the select is on the outside. Should your code be like this:

SELECT ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay]),[TotACDays])) FROM FleetUtilization

 

[jw5107]

hneal98,

I still can't get it to work. Some thing with the "select" part of the SQL that is not getting it....
Don't know where to fix it???

Any other suggetions??
Thanks for the response

 

[hneal98]

What error(s) are you getting?

 

[lespaul]

So you are saying that you copied:

SELECT ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay]),[TotACDays])) FROM FleetUtilization

into the SQL view and it didn't work?



Leslie

 

[jw5107]

To All:

I changed it to:

SELECT(ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay] & ":00,00"),([TotACDays]))) FROM FleetUtilization

I needed to add ":00,00" because the module is formatting the time into milliseconds. I keep getting a syntax error.
I don't know what I am doing!!! I just don't know how to get the code to do the math within a query!!!

The code works fine in a form with a command button like:
Private Sub Command27_Click()
'* Divide a custom time value
Dim ct1 As cTime_struct
Dim ct2 As cTime_struct
Dim ct3 As cTime_struct

'* Parse time strings into cTime_struct variables
ct1 = ctf_Parse(txtDivOp1)

'* Divide the custom time
ct3 = ctf_Divide(ct1, txtDivOp2)

'* Create a string from the cTime_struct variable
txtDivRes = ctf_Construct(ct3)
End Sub

Any other ideas, suggestions!!!

Thanks,
jw

 

[SkipVought]

Hi,

Please explain

1) the input parameters

2) the output objective

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jw5107]

What I am trying to do is divide time. Say 1625:24 (hrs:mins) divided by 8. I have obtained code from peterssoftware.com -"Custom Time Functions". It has examples of doing math with time. The math is dividing the hrs of an aircraft by the usage of an aircraft part.
I reached out to Peter and he gave me:

SELECT(ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay] & ":00,00"),([TotACDays]))) FROM FleetUtilization

I am wanting to do the math via a query, and this was the SQL he suggested. I keep getting a syntax error, and sometimes, when the query is ran, it shutdowns Access completely and creates a backup file. I am thinking that the math is an overload of somesort?? I have seen this when calculating big numbers eg - 47894:16/25 or 63215*14, etc....

jw

 

[SkipVought]

You realize that TIME VALUES are in DAYS, yes?

So 1625:24 divided by 8 is simply

Dim t As Date
t = 1625 / 24 + 24 / 24 / 60
t = t / 8
MsgBox Int(t * 24) & ":" & Int((t * 24 - Int(t * 24)) * 60 + 0.1)

My answer is 203:10.



Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jw5107]

Yes - I understand.

But how do I get this to work in a query?
jw

 

[SkipVought]

I don not understand the functions that you posted. First of all, where does SubSecondsPerSecond come from?

Here's a function that seems to do what you may be looking for

Function TimeString(t As Date) As String
   Dim H1, Hrs As Long, Mins As Long, Secs
   H1 = t * 24
   Hrs = H1
   Mins = (H1 - Hrs) * 60
   Secs = ((H1 - Hrs) * 60 - Mins) * 60
   TimeString = Hrs & ":" & Mins & ":" & Secs
End Function

Seconds could be truncated if another argument were passes to limit the decimal places. Is THAT what SubSecondsPerSecond is supposed to do?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[jw5107]

Still have troubles getting this math to work in a query.

any more suggestions???

This is what I am working with in a query:
FltHrsDay: (SELECT(ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay] & ":00,00"),([TotACDays])))) FROM FleetUtilization)

Everytime query is ran I - Access gets shutdown, and wants to create a backup file?????

Need help!!!!!!!
jw

 

[PHV]

FltHrsDay: (SELECT(ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay] & ":00,00"),([TotACDays])))) FROM FleetUtilization)
No where clause ?
If this subquery returns more than one row, Jet should bomb out...

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

 

[jw5107]

How do I incorporate a WHERE clause??

 

[PHV]

What are you trying to do ?

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

 

[jw5107]

I have a table that has 3 fields like so:

Fleet TotHrsDay TotACDays HrsperDay
727 452:14 234
747 2331:22 245
757 7424:08 1517
767 8285:55 825
A300 3667:02 857
DC-8 1311:02 614
MD-11 2624:06 232

I need to get HrsperDay - I need to divide TotHrsDay by TotACDays. I need for it to be able to calculate over 24hrs. The field TotHrsDay is text data type, and TotACDays is a number. This is probably the problem. Is there a way to do this even though there is a data mismatch?

 

[PHV]

Have you tried to replace this :
FltHrsDay: (SELECT(ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay] & ":00,00"),([TotACDays])))) FROM FleetUtilization)
By this ?
FltHrsDay: ctf_Construct(ctf_Divide(ctf_Parse([TotHrsDay] & ":00,00"),[TotACDays])))

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

 

[MichaelRed]

In another thread you have been given the text to Date-Time (double) conversion:

The Query:

SELECT tblAirCraftType.Fleet, tblAirCraftType.TotHrsDay, tblAirCraftType.TotAcDays, ConvertStringTimeToDate([TotHrsDay])/[TotAcDays] AS MyHrsPerDay
FROM tblAirCraftType
WITH OWNERACCESS OPTION;

returns:

Fleet	TotHrsDay	TotAcDays	MyHrsPerDay
727	452:14	234	8.05258784425451E-02
747	2331:22	245	0.396490929705215
757	7424:08	1517	0.203914890500256
767	8285:55	825	0.41848063973064
A300	3667:02	857	0.17828827952807
DC-8	1311:02	614	8.89680600796236E-02
MD-11	2624:06	232	0.471282327586207

I wonder how much 'experience' you have as a programmer? The two threads I have read seem do not appear to demonstrate a grounding in some of the more basic concepts or logic of programming or database organization. It might be beneficial for you to acquire and study some material on the general topics and specific applications which you need to use.





MichaelRed

 

[jw5107]

MichaelRed,

Well jeez thanks. I appreciate your concerns and wondering of my "experience" as a programmer.

If you have so much "experience" as a programmer then howcome you still didn't help me to solve this issue?? Your code above take - for example the 727 fleet:
452:14/234 = 8.052---- WRONG!!

452:14/234 = 1:55:57

This is the type of result I am looking for. All the code I posted above works fine on a from via a command button. I am just wanting to know how to apply it to a select query with a bunch of junk about experience.

Thanks for the help.
jw

 

[PHV]

Have you tried my last suggestion ?

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