converting number field to hour:minute format 2

[bfamo]

Hi!

I have a number field that contains time usage for a certain project at work. The time has been inserted into the field in minutes with no mask og formating.

To sum up time usage I've got this query:

        qry = "SELECT Sum(Time) AS TotTime" & _
        " FROM QryProject WHERE " & _
        " [District] = 1 "

    rs.Open qry, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Me.TotalTime.Caption = rs!TotTime
    rs.Close

This returns a simple sum of all registered time.
What I need now is for the query to also format the time into hours and minutes.

Any help how to do this would be greatly appreciated!!
Thanks

 

[SkipVought]

Hi,

60 minutes per hour.

Formatted Time String: INT([ElapsedMin]/60) & ":" & ([ElapsedMin]/60 - INT([ElapsedMin]/60)) * 60

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[r937]

SELECT * FROM Minutes;

   n  
     2
    62
    92
   666
   937
  1501
222222


SELECT n
, INT([n]/60) & ":" & ([n]/60 - INT([n]/60)) * 60 AS oneway
, DATEADD("n",n,0) AS anotherway
FROM Minutes;

   n              oneway              anotherway
     2   0:2                                00:02:00
    62   1:2.00000000000001                 01:02:00
    92   1:32                               01:32:00
   666   11:5.99999999999998                11:06:00
   937   15:37                              15:37:00
  1501   25:0.999999999999943    1899-12-31 01:01:00
222222   3703:41.9999999999891   1900-06-02 07:42:00

r937.com | rudy.ca

 

[bfamo]

Hi there, thanks for your posts!

Could you please put the formatting code into the code example I supplied in my first post?

thanks!

 

[PHV]

qry = "SELECT Int(Sum([Time])/60) & ":" & (Sum([Time]) Mod 60) AS TotTime" & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

qry = "SELECT [blue]DATEADD("n",[red]Sum(Time)[/red],0)[/blue] AS TotTime" & _
        " FROM QryProject WHERE " & _
        " [District] = 1 "

r937.com | rudy.ca

 

[bfamo]

Thanks! Almost there. The code looks like this:

        qry = "SELECT Int(Sum([Time])/60) & ":[Red]" & (Sum([Time]) Mod 60) AS TotTime"[/Red] & _ 
        " FROM QryProject WHERE " & _
        " [District] = 1 "

    rs.Open qry, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Me.TotalTime.Caption = rs!TotTime
    rs.Close

When I run the code I get a compile error (highlighted in red), Expected: Line number or label or statement or end of statement.


Any ideas?

 

[HarleyQuinn]

Try:

qry = "SELECT Int(Sum([Time])/60) & " & Chr(34) & ":" & Chr(34) & " & (Sum([Time]) Mod 60) AS TotTime" & _
        " FROM QryProject WHERE " & _
        " [District] = 1 "

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

sorry for the typo:

qry = "SELECT Int(Sum([Time])/60) & ':' & (Sum([Time]) Mod 60) AS TotTime" & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bfamo]

Thanks a lot! looks god

 

[r937]

did you try mine too?

r937.com | rudy.ca