Calling a function "ActualTimeWorked" to Convert Number

[Wasp1]

In my table I have Hrs as a field (Number) and Min as a Field (Number)

I built a query to sum on HRS and Min and use the function below to call from a query, so I can convert 1700.25 to Days, Hrs, Min

I keep getting an error that my expression contains an ambiguous name in my query


Not sure if this Function is setup correctly or my query is incorrect.

Option Compare Database
Option Explicit
 
Public Function ActualTimeWorked(minutes As Long) As String

Dim dd As Integer, hh As Integer, mm As Integer
dd = minutes \ 1440
minutes = minutes - dd * 1440
hh = minutes \ 60
mm = minutes Mod 60
ActualTimeWorked = Format(dd, "000") & ":" _
& Format(hh, "00") & ":" & Format(mm, "00")
End Function

[June7]

The function does not error. It returns 001:04:20.

Must be issue in the query. Post query for analysis.

[ItsMe]

In addition to that... Long data types do not play well with fractions. Perhaps a single or double data type will be better.
Public Function ActualTimeWorked(minutes As Double) As String

[June7]

Good point ItsMe. Did not notice that. The function works but it rounds the minutes input.

[Wasp1]

Sorry if I posted this to a Module in lieu of query.

Here is my SQL

SELECT Sum([Hrs]/60) AS H, Sum(Table1.[Min]) AS M, ActualTimeWorked([H]+[m]) AS Expr1
FROM Table1
GROUP BY ActualTimeWorked([H]+[m]);

[Wasp1]

I attached a copy of the strip down Db.

[June7]

Cannot reference the aggregate function calculated fields within the aggregate query.

SELECT ActualTimeWorked([H]*60+[M]) AS DHM
FROM (SELECT Sum([Hrs]) AS H, Sum([Min]) AS M FROM Table1) AS Q1;

You have data in table that totals 1603 hours and 75 minutes. This is 96255 minutes. This is causing an overflow error in the function. Even after I set all the variables to Double type.

Even in the VBA immediate window:

?1603 * 60

triggers overflow error

Sorry, I don't know how to resolve this.

[Wasp1]

Here is my revision Db

[Wasp1]

I appriciated you trying. Thanks for taking time out to look at this.

[June7]

Some math operations just don't work well in Access, especially when large values are involved.

[ItsMe]

The overflow issue can be overcome but, the math makes no sense to me. You can't convert hours to minutes, add minutes to that, divide by 1440, and then expect the remainder to represent minutes. The remainder will represent a fraction of a whole.

Plus, your query's formula is dividing hours by 60. What is this supposed to do?

I suggest working with the raw data to get your days, minutes, seconds summary. The data in your table seems to be the result of some manipulation.

[June7]

ItsMe, the function on its own works. I tested by calling from VBA immediate window. Input of 1700.25 minutes returns 001:04:20 - days:hours:minutes.
See the revised query in my post 7.
How would you fix the overflow issue?

[ItsMe]

How would you fix the overflow issue?

I would use parenthesis and maybe truncate some of the decimal places in the division areas. Double is a floating point Data type. I think 15 decimals one way OR 15 the other. There is another data type "Decimal" that is fixed and allows a little more in memory allocation. Not my area of expertise though.

As for the function, I did get it to work without creating an error, too. However, when I got to the overflow issue, I started to review the workflow. When I started to analyze the function a little, I noticed that the function depends on a remainder of a whole hour to determine a quantity in seconds. That is where I stopped trying. I am not seeing the logic. I did not spend a lot of time on it and I could be wrong but, not seeing it.

[June7]

The function returns days:min:hrs in a string. Seconds are not retained. The integer datatype causes each calculated value to be rounded.

The function won't even run at all with input of 96255 minutes. I put a breakpoint on the first line and doesn't even get there. It works up 33119.499.

[ItsMe]

An Access Integer is good for -32,768 to 32,767. This is why the ceiling you are describing and it breaking that way (nothing new there). I went straight to Double type when testing and then ran into the overflow issue (using the Double type). I failed to mention that earlier because I was going on an assumption that the Long would was being avoided.

I will take another look but, I think the function is flawed from the onset. So I do not see any benefit in fixing the overflow issue. Aside from that, the business rules may allow for a more eloquent approach.