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
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