Convert Negative values to zero

[Majic]

I calculated downtime using a query and now I need to change negative
numbers (not downtime) to zeros.

Please let me know how I can do that.

Thank you

 

[John W. Vinson]

I calculated downtime using a query and now I need to change negative
numbers (not downtime) to zeros.

Please let me know how I can do that.

Thank you

IIF([Downtime] < 0, 0, [Downtime])

John W. Vinson [MVP]

 

[Majic]

I calculated downtime using a query and now I need to change negative
numbers (not downtime) to zeros.

Please let me know how I can do that.

Thank you

IIF([Downtime] < 0, 0, [Downtime])

John W. Vinson [MVP]

 

[Majic]

I calculated downtime using a query and now I need to change negative
numbers (not downtime) to zeros.

Please let me know how I can do that.

Thank you

IIF([Downtime] < 0, 0, [Downtime])

John W. Vinson [MVP]

Thank you John,
your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report
for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Thank you for all your help

 

[John W. Vinson]

I calculated downtime using a query and now I need to change negative
numbers (not downtime) to zeros.

Please let me know how I can do that.

Thank you

IIF([Downtime] < 0, 0, [Downtime])

John W. Vinson [MVP]

Thank you John,
your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report
for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Please post the SQL of the query and the error message.

John W. Vinson [MVP]

 

[Majic]

I calculated downtime using a query and now I need to change negative
numbers (not downtime) to zeros.
Please let me know how I can do that.
Thank you
IIF([Downtime] < 0, 0, [Downtime])
John W. Vinson [MVP]

Thank you John,
your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report
for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Please post the SQL of the query and the error message.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Here is the SQL query:SELECT TimeSheet.OperatorID, TimeSheet.omo,
TimeSheet.StationID, TimeSheet.[Date Tested], TimeSheet.TimeIn,
TimeSheet.TimeOut, HoursAndMinutes([TimeOut]-[TimeIn]) AS Total,
TimeSheet.TaktTime, ([Total]-[TaktTime]) AS TotalHours,
IIf([TotalHours]<0,0,[TotalHours]) AS Downtime
FROM [Time Cards] RIGHT JOIN TimeSheet ON [Time Cards].EmployeeID =
TimeSheet.OperatorID
GROUP BY TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
TimeSheet.TaktTime, IIf([TotalHours]<0,0,[TotalHours]);

I need to total Downtime value after converting TotalHours (negative
to 0)

Thank you

 

[Majic]

I calculated downtime using a query and now I need to change negative
numbers (not downtime) to zeros.
Please let me know how I can do that.
Thank you
IIF([Downtime] < 0, 0, [Downtime])
John W. Vinson [MVP]

Thank you John,
your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report
for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Please post the SQL of the query and the error message.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John, I am not sure if you or Bruce could help me on this. I really
need your guys help, please find the detail of the problwm below

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.


Please let me know what I can do, keep in mind that I need it in
decimal time.


Thank you