# Unresolved Issue about totaling Downtime Hours

M

#### Majic

Hey Guys,
I am really desperate to resolve this issue. Below, you will detail
information about the problem. I have the calculation working upto
calculating downtime even converting negative numbers to 0. The
problem is trying to total downtime in query or report. It gives me
the following error:

The error message "The expression is typed incorrectly, or is too
complex to be evaluated. For example a numeric expression may
contain
too many complicated elements. Try simplifying the expression by
assigning parts of the expression to variable". This message when I
have TotalDowntime: Sum([Downtime])

The reason I am using HoursAndMinutes to convert my time to decimal
and here is the code that I am using as a macro:
Option Compare Database
Option Explicit

Public Function HoursAndMinutes(interval As Variant) As String

Dim totalminutes As Long, totalseconds As Long
Dim Hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

Hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

If minutes > 59 Then Hours = Hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = Hours & "." & Format(minutes * 1.666666667, "00")
End Function

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd
As Date) As String

Dim interval As Double, str As String, days As Variant
Dim Hours As String, minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
Hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(Hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(Hours = "0", "", _
IIf(Hours = "1", Hours & " Hour", Hours & " Hours"))
str = str & IIf(Hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function

Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As
Date) As String

Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function

Here is what the query and calculations:
I have the following fields: TimeIn, TimeOut, TaktTime which is 0.58
(after converting to decimal it is actually 35 minutes), Total
(TimeOut-TimeIn) and I used Total: HoursAndMinutes([TimeOut]-
[TimeIn]) , TotalHours and I used
TotalHours: ([Total]-[TaktTime]) , Downtime: IIf([TotalHours]<0,0,
[TotalHours]) in order to convert negative numbers from TotalHours to
zeros and now I need to total downtime and I get the above message.

I need all the help I can get. I have couple of guys helping me on
this and I need one more thing to be abel to total Downtime.

Thank you to all

G

#### Guest

Your functions basically return strings and then it seems you expect to do
math with the string values. If your results are going to a form or report,
do the formatting there with only date/time or numeric values. Don't do any
formatting or other stuff in the query.
--
Duane Hookom
Microsoft Access MVP

Majic said:
Hey Guys,
I am really desperate to resolve this issue. Below, you will detail
information about the problem. I have the calculation working upto
calculating downtime even converting negative numbers to 0. The
problem is trying to total downtime in query or report. It gives me
the following error:

The error message "The expression is typed incorrectly, or is too
complex to be evaluated. For example a numeric expression may
contain
too many complicated elements. Try simplifying the expression by
assigning parts of the expression to variable". This message when I
have TotalDowntime: Sum([Downtime])

The reason I am using HoursAndMinutes to convert my time to decimal
and here is the code that I am using as a macro:
Option Compare Database
Option Explicit

Public Function HoursAndMinutes(interval As Variant) As String

Dim totalminutes As Long, totalseconds As Long
Dim Hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

Hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

If minutes > 59 Then Hours = Hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = Hours & "." & Format(minutes * 1.666666667, "00")
End Function

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd
As Date) As String

Dim interval As Double, str As String, days As Variant
Dim Hours As String, minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
Hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(Hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(Hours = "0", "", _
IIf(Hours = "1", Hours & " Hour", Hours & " Hours"))
str = str & IIf(Hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function

Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As
Date) As String

Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function

Here is what the query and calculations:
I have the following fields: TimeIn, TimeOut, TaktTime which is 0.58
(after converting to decimal it is actually 35 minutes), Total
(TimeOut-TimeIn) and I used Total: HoursAndMinutes([TimeOut]-
[TimeIn]) , TotalHours and I used
TotalHours: ([Total]-[TaktTime]) , Downtime: IIf([TotalHours]<0,0,
[TotalHours]) in order to convert negative numbers from TotalHours to
zeros and now I need to total downtime and I get the above message.

I need all the help I can get. I have couple of guys helping me on
this and I need one more thing to be abel to total Downtime.

Thank you to all