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

 

[BruceM]

On Jul 20, 4:17 pm, 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]- 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

As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a few
observations. Is HoursAndMinutes a user-defined function? Unless it does
more than subtract TimeIn from TimeOut, I don't think it is necessary. You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a mathematical
operation in the same query. You need to use the calculation itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours

The same thing applies to TotalHours later in the SQL. One way around this
is to use a separate named query as the source for this query. In that case
you can perform calculations with the alias fields. You can also do this
within a single query, but I doubt I would be able to provide accurate
syntax.

 

[Majic]

On Jul 20, 4:17 pm, 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]- 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

As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a few
observations. Is HoursAndMinutes a user-defined function? Unless it does
more than subtract TimeIn from TimeOut, I don't think it is necessary. You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a mathematical
operation in the same query. You need to use the calculation itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours

The same thing applies to TotalHours later in the SQL. One way around this
is to use a separate named query as the source for this query. In that case
you can perform calculations with the alias fields. You can also do this
within a single query, but I doubt I would be able to provide accurate
syntax.- Hide quoted text -

- Show quoted text -

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

 

[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

 

[BruceM]

As I said, I don't think you can create an alias, then use the alias
directly in a calculation. You could create the following, and save the
query:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet

Assuming that it works properly, use the named query as the source for a new
query that includes the calculations.

You can also do something like:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
AS Q1
(Q1.[Total]-[TaktTime]) AS TotalHours
and so forth, but I am not quite sure of the syntax, partly because I don't
fully understand what you are trying to do. This is not because of
insufficient explanation on your part, but rather insufficient knowledge on
mine. I answered a rather straightforward question early in the thread, but
the follow-up questions have gotten beyond my area of confident knowledge.

On Jul 25, 12:56 pm, John W. Vinson
On Jul 20, 4:17 pm, 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]- 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

As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a few
observations. Is HoursAndMinutes a user-defined function? Unless it
does
more than subtract TimeIn from TimeOut, I don't think it is necessary.
You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a mathematical
operation in the same query. You need to use the calculation itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours

The same thing applies to TotalHours later in the SQL. One way around
this
is to use a separate named query as the source for this query. In that
case
you can perform calculations with the alias fields. You can also do this
within a single query, but I doubt I would be able to provide accurate
syntax.- Hide quoted text -

- Show quoted text -

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

 

[Majic]

As I said, I don't think you can create an alias, then use the alias
directly in a calculation. You could create the following, and save the
query:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet

Assuming that it works properly, use the named query as the source for a new
query that includes the calculations.

You can also do something like:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
AS Q1
(Q1.[Total]-[TaktTime]) AS TotalHours
and so forth, but I am not quite sure of the syntax, partly because I don't
fully understand what you are trying to do. This is not because of
insufficient explanation on your part, but rather insufficient knowledge on
mine. I answered a rather straightforward question early in the thread, but
the follow-up questions have gotten beyond my area of confident knowledge.

On Jul 25, 12:56 pm, John W. Vinson
On Jul 20, 4:17 pm, 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]- 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
As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a few
observations. Is HoursAndMinutes a user-defined function? Unless it
does
more than subtract TimeIn from TimeOut, I don't think it is necessary.
You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a mathematical
operation in the same query. You need to use the calculation itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours
The same thing applies to TotalHours later in the SQL. One way around
this
is to use a separate named query as the source for this query. In that
case
you can perform calculations with the alias fields. You can also do this
within a single query, but I doubt I would be able to provide accurate
syntax.- Hide quoted text -
- Show quoted text -

Bruce,
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- Hide quoted text -

- Show quoted text -

Thank you for your assistance. I am still getting the same result. I
do have the calculation done upto Downtime hours and the problem is
after the conversion of negative to zero, I am not able to add those
Downtimes in query or report.
I appreciate all your help on this. I will keep trying to see if I
have a solution for this.

Thank you

 

[BruceM]

As I said, I don't think you can create an alias, then use the alias
directly in a calculation. You could create the following, and save the
query:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet

Assuming that it works properly, use the named query as the source for a
new
query that includes the calculations.

You can also do something like:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
AS Q1
(Q1.[Total]-[TaktTime]) AS TotalHours
and so forth, but I am not quite sure of the syntax, partly because I
don't
fully understand what you are trying to do. This is not because of
insufficient explanation on your part, but rather insufficient knowledge
on
mine. I answered a rather straightforward question early in the thread,
but
the follow-up questions have gotten beyond my area of confident
knowledge.

On Jul 25, 12:56 pm, John W. Vinson
On Jul 20, 4:17 pm, 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]- 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

As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a few
observations. Is HoursAndMinutes a user-defined function? Unless it
does
more than subtract TimeIn from TimeOut, I don't think it is necessary.
You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a
mathematical
operation in the same query. You need to use the calculation itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours

The same thing applies to TotalHours later in the SQL. One way around
this
is to use a separate named query as the source for this query. In
that
case
you can perform calculations with the alias fields. You can also do
this
within a single query, but I doubt I would be able to provide accurate
syntax.- Hide quoted text -

- Show quoted text -

Bruce,
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- Hide quoted text -

- Show quoted text -

Thank you for your assistance. I am still getting the same result. I
do have the calculation done upto Downtime hours and the problem is
after the conversion of negative to zero, I am not able to add those
Downtimes in query or report.
I appreciate all your help on this. I will keep trying to see if I
have a solution for this.

Thank you

You can add them in a report by putting the field (the Downtime one with the
0 conversion for negative numbers) into a text box on a report, and setting
its Running Sum property to Over All (or maybe Over Group, if you are using
grouping in the report). If the text box is named txtDowntime, you can make
it invisible, and put an unbound text box into the report footer, with the
text box control source set to =[txtDowntime].
In a query, try DSum:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours]","TimeSheet")
This will give you the sum for all records. Sum works within a record,
although in a Totals query it can give you the same result as DSum if you
are not grouping any of the fields. I understand how that works better than
I can explain it, so unless it matters for your project I will not try to
explain further.

 

[Majic]

As I said, I don't think you can create an alias, then use the alias
directly in a calculation. You could create the following, and save the
query:
SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
Assuming that it works properly, use the named query as the source for a
new
query that includes the calculations.
You can also do something like:
SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
AS Q1
(Q1.[Total]-[TaktTime]) AS TotalHours
and so forth, but I am not quite sure of the syntax, partly because I
don't
fully understand what you are trying to do. This is not because of
insufficient explanation on your part, but rather insufficient knowledge
on
mine. I answered a rather straightforward question early in the thread,
but
the follow-up questions have gotten beyond my area of confident
knowledge.


On Jul 25, 12:56 pm, John W. Vinson
On Jul 20, 4:17 pm, 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]- 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
As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a few
observations. Is HoursAndMinutes a user-defined function? Unless it
does
more than subtract TimeIn from TimeOut, I don't think it is necessary.
You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a
mathematical
operation in the same query. You need to use the calculation itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours
The same thing applies to TotalHours later in the SQL. One way around
this
is to use a separate named query as the source for this query. In
that
case
you can perform calculations with the alias fields. You can also do
this
within a single query, but I doubt I would be able to provide accurate
syntax.- Hide quoted text -
- Show quoted text -
Bruce,
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- Hide quoted text -
- Show quoted text -

Thank you for your assistance. I am still getting the same result. I
do have the calculation done upto Downtime hours and the problem is
after the conversion of negative to zero, I am not able to add those
Downtimes in query or report.
I appreciate all your help on this. I will keep trying to see if I
have a solution for this.

Thank you

You can add them in a report by putting the field (the Downtime one with the
0 conversion for negative numbers) into a text box on a report, and setting
its Running Sum property to Over All (or maybe Over Group, if you are using
grouping in the report). If the text box is named txtDowntime, you can make
it invisible, and put an unbound text box into the report footer, with the
text box control source set to =[txtDowntime].
In a query, try DSum:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours]","TimeSheet")
This will give you the sum for all records. Sum works within a record,
although in a Totals query it can give you the same result as DSum if you
are not grouping any of the fields. I understand how that works better than
I can explain it, so unless it matters for your project I will not try to
explain further.- Hide quoted text -

- Show quoted text -

Please do explain some more. I did add DSum and it is missing
parenthesis I am assumin it should go like this:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours])","TimeSheet") It
did not give me an error however it does not give me any values. I am
not grouping and I really prefer to do it in query so I can pull a
chart also.

Please advise what I should do next.

Thank you for your effort

 

[BruceM]

As I said, I don't think you can create an alias, then use the alias
directly in a calculation. You could create the following, and save
the
query:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet

Assuming that it works properly, use the named query as the source for
a
new
query that includes the calculations.

You can also do something like:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
AS Q1
(Q1.[Total]-[TaktTime]) AS TotalHours
and so forth, but I am not quite sure of the syntax, partly because I
don't
fully understand what you are trying to do. This is not because of
insufficient explanation on your part, but rather insufficient
knowledge
on
mine. I answered a rather straightforward question early in the
thread,
but
the follow-up questions have gotten beyond my area of confident
knowledge.

On Jul 25, 12:56 pm, John W. Vinson
On Jul 20, 4:17 pm, John W. Vinson
@aol.com>
wrote:
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

As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a
few
observations. Is HoursAndMinutes a user-defined function? Unless
it
does
more than subtract TimeIn from TimeOut, I don't think it is
necessary.
You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a
mathematical
operation in the same query. You need to use the calculation
itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours

The same thing applies to TotalHours later in the SQL. One way
around
this
is to use a separate named query as the source for this query. In
that
case
you can perform calculations with the alias fields. You can also
do
this
within a single query, but I doubt I would be able to provide
accurate
syntax.- Hide quoted text -

- Show quoted text -

Bruce,
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- Hide quoted text -

- Show quoted text -

Thank you for your assistance. I am still getting the same result. I
do have the calculation done upto Downtime hours and the problem is
after the conversion of negative to zero, I am not able to add those
Downtimes in query or report.
I appreciate all your help on this. I will keep trying to see if I
have a solution for this.

Thank you

You can add them in a report by putting the field (the Downtime one with
the
0 conversion for negative numbers) into a text box on a report, and
setting
its Running Sum property to Over All (or maybe Over Group, if you are
using
grouping in the report). If the text box is named txtDowntime, you can
make
it invisible, and put an unbound text box into the report footer, with
the
text box control source set to =[txtDowntime].
In a query, try DSum:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours]","TimeSheet")
This will give you the sum for all records. Sum works within a record,
although in a Totals query it can give you the same result as DSum if you
are not grouping any of the fields. I understand how that works better
than
I can explain it, so unless it matters for your project I will not try to
explain further.- Hide quoted text -

- Show quoted text -

Please do explain some more. I did add DSum and it is missing
parenthesis I am assumin it should go like this:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours])","TimeSheet") It
did not give me an error however it does not give me any values. I am
not grouping and I really prefer to do it in query so I can pull a
chart also.

Please advise what I should do next.

Thank you for your effort

You are correct. I omitted a closing parentheses.

Add a field to the query:
TestDownTime: IIf([TotalHours]<0,0,[TotalHours])
Do you see a value for each record?

 

[Majic]

As I said, I don't think you can create an alias, then use the alias
directly in a calculation. You could create the following, and save
the
query:
SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
Assuming that it works properly, use the named query as the source for
a
new
query that includes the calculations.
You can also do something like:
SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
AS Q1
(Q1.[Total]-[TaktTime]) AS TotalHours
and so forth, but I am not quite sure of the syntax, partly because I
don't
fully understand what you are trying to do. This is not because of
insufficient explanation on your part, but rather insufficient
knowledge
on
mine. I answered a rather straightforward question early in the
thread,
but
the follow-up questions have gotten beyond my area of confident
knowledge.


On Jul 25, 12:56 pm, John W. Vinson
On Jul 20, 4:17 pm, John W. Vinson
@aol.com>
wrote:
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
As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt a
few
observations. Is HoursAndMinutes a user-defined function? Unless
it
does
more than subtract TimeIn from TimeOut, I don't think it is
necessary.
You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a
mathematical
operation in the same query. You need to use the calculation
itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours
The same thing applies to TotalHours later in the SQL. One way
around
this
is to use a separate named query as the source for this query. In
that
case
you can perform calculations with the alias fields. You can also
do
this
within a single query, but I doubt I would be able to provide
accurate
syntax.- Hide quoted text -
- Show quoted text -
Bruce,
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- Hide quoted text -
- Show quoted text -
Thank you for your assistance. I am still getting the same result. I
do have the calculation done upto Downtime hours and the problem is
after the conversion of negative to zero, I am not able to add those
Downtimes in query or report.
I appreciate all your help on this. I will keep trying to see if I
have a solution for this.
Thank you
You can add them in a report by putting the field (the Downtime one with
the
0 conversion for negative numbers) into a text box on a report, and
setting
its Running Sum property to Over All (or maybe Over Group, if you are
using
grouping in the report). If the text box is named txtDowntime, you can
make
it invisible, and put an unbound text box into the report footer, with
the
text box control source set to =[txtDowntime].
In a query, try DSum:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours]","TimeSheet")
This will give you the sum for all records. Sum works within a record,
although in a Totals query it can give you the same result as DSum if you
are not grouping any of the fields. I understand how that works better
than
I can explain it, so unless it matters for your project I will not try to
explain further.- Hide quoted text -
- Show quoted text -

Please do explain some more. I did add DSum and it is missing
parenthesis I am assumin it should go like this:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours])","TimeSheet") It
did not give me an error however it does not give me any values. I am
not grouping and I really prefer to do it in query so I can pull a
chart also.

Please advise what I should do next.

Thank you for your effort

You are correct. I omitted a closing parentheses.

Add a field to the query:
TestDownTime: IIf([TotalHours]<0,0,[TotalHours])
Do you see a value for each record?

Yes, I do. However this is the total, this is an individual downtime.
This is the statement that you gave me before to convert values from
negative to 0 and it works. Now, I just need to total them.

Thank you

 

[BruceM]

I should tell you I will be away until Monday, and will not be able to look
at this any more today. I would suggest the queries group for a place to
post a new thread. Sorry I couldn't be of any more help than I was.

As I said, I don't think you can create an alias, then use the
alias
directly in a calculation. You could create the following, and
save
the
query:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet

Assuming that it works properly, use the named query as the source
for
a
new
query that includes the calculations.

You can also do something like:

SELECT OperatorID, omo,
StationID, [Date Tested], TimeIn,
TimeOut, TaktTime,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total
FROM TimeSheet
AS Q1
(Q1.[Total]-[TaktTime]) AS TotalHours
and so forth, but I am not quite sure of the syntax, partly because
I
don't
fully understand what you are trying to do. This is not because of
insufficient explanation on your part, but rather insufficient
knowledge
on
mine. I answered a rather straightforward question early in the
thread,
but
the follow-up questions have gotten beyond my area of confident
knowledge.

On Jul 25, 12:56 pm, John W. Vinson
@aol.com>
wrote:
On Jul 20, 4:17 pm, John W. Vinson
@aol.com>
wrote:
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

As John asked, what is the error message?
I am relatively inexperienced at reading SQL, but I will attempt
a
few
observations. Is HoursAndMinutes a user-defined function?
Unless
it
does
more than subtract TimeIn from TimeOut, I don't think it is
necessary.
You
should be able to have:
[TimeOut]-[TimeIn] AS Total
Also, I don't think you can use the alias field Total in a
mathematical
operation in the same query. You need to use the calculation
itself:
[TimeOut]-[TimeIn]-[TaktTime] AS TotalHours

The same thing applies to TotalHours later in the SQL. One way
around
this
is to use a separate named query as the source for this query.
In
that
case
you can perform calculations with the alias fields. You can
also
do
this
within a single query, but I doubt I would be able to provide
accurate
syntax.- Hide quoted text -

- Show quoted text -

Bruce,
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- Hide quoted text -

- Show quoted text -

Thank you for your assistance. I am still getting the same result.
I
do have the calculation done upto Downtime hours and the problem is
after the conversion of negative to zero, I am not able to add those
Downtimes in query or report.
I appreciate all your help on this. I will keep trying to see if I
have a solution for this.

Thank you

You can add them in a report by putting the field (the Downtime one
with
the
0 conversion for negative numbers) into a text box on a report, and
setting
its Running Sum property to Over All (or maybe Over Group, if you are
using
grouping in the report). If the text box is named txtDowntime, you
can
make
it invisible, and put an unbound text box into the report footer, with
the
text box control source set to =[txtDowntime].
In a query, try DSum:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours]","TimeSheet")
This will give you the sum for all records. Sum works within a
record,
although in a Totals query it can give you the same result as DSum if
you
are not grouping any of the fields. I understand how that works
better
than
I can explain it, so unless it matters for your project I will not try
to
explain further.- Hide quoted text -

- Show quoted text -

Please do explain some more. I did add DSum and it is missing
parenthesis I am assumin it should go like this:
Downtime: DSum("IIf([TotalHours]<0,0,[TotalHours])","TimeSheet") It
did not give me an error however it does not give me any values. I am
not grouping and I really prefer to do it in query so I can pull a
chart also.

Please advise what I should do next.

Thank you for your effort

You are correct. I omitted a closing parentheses.

Add a field to the query:
TestDownTime: IIf([TotalHours]<0,0,[TotalHours])
Do you see a value for each record?

Yes, I do. However this is the total, this is an individual downtime.
This is the statement that you gave me before to convert values from
negative to 0 and it works. Now, I just need to total them.

Thank you