Create/Use function

[Wes via AccessMonster.com]

Have a long integer field that represents seconds. I use a query to total
seconds. Then in the query under SQL I put a format statement to convert
total seconds to days hh:nn:ss. This works very well, but the format
statement is long and confusing to read.

FORMAT$([Sum OF Duration]\86400, "0")&" day "&FORMAT$(([Sum OF Duration]MOD
86400)\3600,"00")&":"&FORMAT$(([Sum OF Duration]MOD 3600)\60,"00")&":"&FORMAT
$([Sum OF Duration]MOD 60,"00") AS Total_Air_Time

See what I mean?

So, I thought about appling a function to do this. Started writing the
function and tested it and an error comes up that has me stumped.

Wrong number of arugments used with function in query expression 'dhns([secs])
'.

So my first question is - Can I use a function to format a field?
Second question - What and how many arguments is the function looking for.

Here is the function code. I realize that I still have to add some lines to
format the result, but was testing the code when the argument error came up.


Public Function dhns()

Dim myDay As Integer
Dim myHours As Integer
Dim myMins As Integer
Dim mySecs As Integer

myDay = (sumofduration) \ 86400
myHours = myDay Mod 86400 \ 3600
myMins = myHours Mod 3600 \ 60
mySecs = mymymins Mod 60

End Function

 

[Allan Murphy]

It should read

public function dhns(sumofduration)
Dim myDay As Integer
Dim myHours As Integer
Dim myMins As Integer
Dim mySecs As Integer

myDay = (sumofduration) \ 86400
myHours = myDay Mod 86400 \ 3600
myMins = myHours Mod 3600 \ 60
mySecs = mymymins Mod 60

dhns=myDay &":" & myHours & ":" & myMin &":" & mySecs

End Function

 

[Douglas J Steele]

Public Function dhns(InputValue As Variant) As String

Dim myDay As Integer
Dim myHours As Integer
Dim myMins As Integer
Dim mySecs As Integer

If IsNull(InputValue) = False Then
myDay = InputValue \ 86400
myHours = (myDay Mod 86400) \ 3600
myMins = (myHours Mod 3600) \ 60
mySecs = myMins Mod 60

If myDay = 1 Then
dhns = "1 day " & _
Format$(myHours, "00") & ":" & _
Format$(myMins, "00") & ":" & _
Format$(mySecs, "00")

Else
dhns = Format$(myDay, "0") & " days " & _
Format$(myHours, "00") & ":" & _
Format$(myMins, "00") & ":" & _
Format$(mySecs, "00")
End If
Else
dhns = "Invalid Input"
End If

End Function

Change your query to dhns([Sum OF Duration]) AS Total_Air_Time

Note that you can only do this if you're running the query from within
Access. If you're trying to run the query from outside of Access (say, from
VB), it won't work.

 

[Wes via AccessMonster.com]

Excellent help! It is working like a dream! Had to change the code just a bit,
but the syntax is a big help!

Thanks for your help. Here is the finished product...

Public Function dhns(SumOfDuration As Variant) As String

Dim myDay As Integer
Dim myHours As Integer
Dim myMins As Integer
Dim mySecs As Integer

If IsNull(SumOfDuration) = False Then
myDay = SumOfDuration \ 86400
myHours = (SumOfDuration Mod 86400) \ 3600
myMins = (SumOfDuration Mod 3600) \ 60
mySecs = SumOfDuration Mod 60

If myDay = 1 Then
dhns = "1 day " & _
Format$(myHours, "00") & ":" & _
Format$(myMins, "00") & ":" & _
Format$(mySecs, "00")

Else
dhns = Format$(myDay, "0") & " days " & _
Format$(myHours, "00") & ":" & _
Format$(myMins, "00") & ":" & _
Format$(mySecs, "00")
End If
Else
dhns = "Invalid Input"
End If

End Function

Public Function dhns(InputValue As Variant) As String

Dim myDay As Integer
Dim myHours As Integer
Dim myMins As Integer
Dim mySecs As Integer

If IsNull(InputValue) = False Then
myDay = InputValue \ 86400
myHours = (myDay Mod 86400) \ 3600
myMins = (myHours Mod 3600) \ 60
mySecs = myMins Mod 60

If myDay = 1 Then
dhns = "1 day " & _
Format$(myHours, "00") & ":" & _
Format$(myMins, "00") & ":" & _
Format$(mySecs, "00")

Else
dhns = Format$(myDay, "0") & " days " & _
Format$(myHours, "00") & ":" & _
Format$(myMins, "00") & ":" & _
Format$(mySecs, "00")
End If
Else
dhns = "Invalid Input"
End If

End Function

Change your query to dhns([Sum OF Duration]) AS Total_Air_Time

Note that you can only do this if you're running the query from within
Access. If you're trying to run the query from outside of Access (say, from
VB), it won't work.

Have a long integer field that represents seconds. I use a query to total
seconds. Then in the query under SQL I put a format statement to convert

[quoted text clipped - 32 lines]

End Function