If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Convert Negative values to zero
On Jul 26, 9:14 am, "BruceM" wrote:
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. "Majic" wrote in message ups.com... On Jul 25, 3:59 pm, "BruceM" wrote: "Majic" wrote in message roups.com... On Jul 25, 12:56 pm, John W. Vinson wrote: On Wed, 25 Jul 2007 07:23:43 -0700, Majic wrote: On Jul 20, 4:17 pm, John W. Vinson wrote: On Fri, 20 Jul 2007 11:08:39 -0700, Majic 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 |
#12
|
|||
|
|||
Convert Negative values to zero
"Majic" wrote in message ps.com... On Jul 26, 9:14 am, "BruceM" wrote: 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. "Majic" wrote in message ups.com... On Jul 25, 3:59 pm, "BruceM" wrote: "Majic" wrote in message roups.com... On Jul 25, 12:56 pm, John W. Vinson wrote: On Wed, 25 Jul 2007 07:23:43 -0700, Majic wrote: On Jul 20, 4:17 pm, John W. Vinson wrote: On Fri, 20 Jul 2007 11:08:39 -0700, Majic 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. |
#13
|
|||
|
|||
Convert Negative values to zero
On Jul 26, 11:04 am, "BruceM" wrote:
"Majic" wrote in message ps.com... On Jul 26, 9:14 am, "BruceM" wrote: 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. "Majic" wrote in message roups.com... On Jul 25, 3:59 pm, "BruceM" wrote: "Majic" wrote in message roups.com... On Jul 25, 12:56 pm, John W. Vinson wrote: On Wed, 25 Jul 2007 07:23:43 -0700, Majic wrote: On Jul 20, 4:17 pm, John W. Vinson wrote: On Fri, 20 Jul 2007 11:08:39 -0700, Majic 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 |
#14
|
|||
|
|||
Convert Negative values to zero
"Majic" wrote in message
ps.com... On Jul 26, 11:04 am, "BruceM" wrote: "Majic" wrote in message ps.com... On Jul 26, 9:14 am, "BruceM" wrote: 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. "Majic" wrote in message roups.com... On Jul 25, 3:59 pm, "BruceM" wrote: "Majic" wrote in message roups.com... On Jul 25, 12:56 pm, John W. Vinson wrote: On Wed, 25 Jul 2007 07:23:43 -0700, Majic wrote: On Jul 20, 4:17 pm, John W. Vinson wrote: On Fri, 20 Jul 2007 11:08:39 -0700, Majic 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? |
#15
|
|||
|
|||
Convert Negative values to zero
On Jul 26, 1:40 pm, "BruceM" wrote:
"Majic" wrote in message ps.com... On Jul 26, 11:04 am, "BruceM" wrote: "Majic" wrote in message oups.com... On Jul 26, 9:14 am, "BruceM" wrote: 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. "Majic" wrote in message roups.com... On Jul 25, 3:59 pm, "BruceM" wrote: "Majic" wrote in message roups.com... On Jul 25, 12:56 pm, John W. Vinson wrote: On Wed, 25 Jul 2007 07:23:43 -0700, Majic wrote: On Jul 20, 4:17 pm, John W. Vinson wrote: On Fri, 20 Jul 2007 11:08:39 -0700, Majic 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 |
#16
|
|||
|
|||
Convert Negative values to zero
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. "Majic" wrote in message ups.com... On Jul 26, 1:40 pm, "BruceM" wrote: "Majic" wrote in message ps.com... On Jul 26, 11:04 am, "BruceM" wrote: "Majic" wrote in message oups.com... On Jul 26, 9:14 am, "BruceM" wrote: 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. "Majic" wrote in message roups.com... On Jul 25, 3:59 pm, "BruceM" wrote: "Majic" wrote in message roups.com... On Jul 25, 12:56 pm, John W. Vinson wrote: On Wed, 25 Jul 2007 07:23:43 -0700, Majic wrote: On Jul 20, 4:17 pm, John W. Vinson wrote: On Fri, 20 Jul 2007 11:08:39 -0700, Majic 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 |
|
Thread Tools | |
Display Modes | |
|
|