Unresolved Issue about totaling Downtime Hours

  • Thread starter Thread starter Majic
  • Start date Start date
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
 
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
Click to expand...
 
Back
Top