Calculate Time in A Report
in [Reports]
|
From: PolQueen on 18 Mar 2010 14:34 I know that working with time in Access can be tricky. I have multiple locations that I am tracking times that vehicles are covering for another vehicle, and I really need to use time format. When I have only a few entries, the first formula works with no problem, but with a lot of entries, it is incorrect: =Sum([StartTime]-[ EndTime]) Format: Short Time Input Mask: 00:00;0;_ (25 entries ranging from :45 minutes to 1:20 minutes = 3:05 hours, which is wrong.) When I have a lot of entries (some are in the hundreds), the above formula is completely wrong. I can get close to the correct number with the formula shown below, but it is no longer in time format and I am questioned why something is out of service for 27.08 hours when everything is always in five minute increments. =Sum(DateDiff("n",[StartTime],[EndTime])/60) Format: Fixed Decimal Places: 2 (The same 25 entries ranging from :45 minutes to 1:20 minutes = 27:08 hours, is actually 27:05 hours). What is the proper way to calculate this so that I get the true time? Thank you.
From: John Spencer on 18 Mar 2010 15:26 Sum(DateDiff("n",[StartTime],[EndTime]) gives you the number of minutes Sum(DateDiff("n",[StartTime],[EndTime])\60 gives you the number of hours (that is integer division so it removes the fractional portion). Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60 gives you the number of leftover minutes. So for display purposes in hours and minutes: Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") 27.08 is 27 hours and 5 minutes (actually the number is probably 27.08333333333 but your 2 decimal places is rounding that off). Your problem with the first solution is you are attempting to show a duration of time using a point in time. 27 hours and 5 minutes. A point in time is never going to exceed 24 hours. Once it does, you are going to end up with a day (date) plus the time left over after you divide by 24 hours. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County PolQueen wrote: > I know that working with time in Access can be tricky. I have multiple > locations that I am tracking times that vehicles are covering for another > vehicle, and I really need to use time format. When I have only a few > entries, the first formula works with no problem, but with a lot of entries, > it is incorrect: > > =Sum([StartTime]-[ EndTime]) > Format: Short Time > Input Mask: 00:00;0;_ > (25 entries ranging from :45 minutes to 1:20 minutes = 3:05 hours, which is > wrong.) When I have a lot of entries (some are in the hundreds), the above > formula is completely wrong. > > I can get close to the correct number with the formula shown below, but it > is no longer in time format and I am questioned why something is out of > service for 27.08 hours when everything is always in five minute increments. > > =Sum(DateDiff("n",[StartTime],[EndTime])/60) > Format: Fixed > Decimal Places: 2 > (The same 25 entries ranging from :45 minutes to 1:20 minutes = 27:08 hours, > is actually 27:05 hours). > > What is the proper way to calculate this so that I get the true time? > > Thank you. >
From: PolQueen on 18 Mar 2010 16:23 Thank you, John. When I try to use: Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") I get this message: Syntax error in query expression 'First([Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")])' Should I be doing something else? Thank you. "John Spencer" wrote: > Sum(DateDiff("n",[StartTime],[EndTime]) gives you the number of minutes > > Sum(DateDiff("n",[StartTime],[EndTime])\60 gives you the number of hours (that > is integer division so it removes the fractional portion). > > Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60 gives you the number of > leftover minutes. > > So for display purposes in hours and minutes: > Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & > Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") > > 27.08 is 27 hours and 5 minutes (actually the number is probably > 27.08333333333 but your 2 decimal places is rounding that off). > > Your problem with the first solution is you are attempting to show a duration > of time using a point in time. 27 hours and 5 minutes. A point in time is > never going to exceed 24 hours. Once it does, you are going to end up with a > day (date) plus the time left over after you divide by 24 hours. > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > PolQueen wrote: > > I know that working with time in Access can be tricky. I have multiple > > locations that I am tracking times that vehicles are covering for another > > vehicle, and I really need to use time format. When I have only a few > > entries, the first formula works with no problem, but with a lot of entries, > > it is incorrect: > > > > =Sum([StartTime]-[ EndTime]) > > Format: Short Time > > Input Mask: 00:00;0;_ > > (25 entries ranging from :45 minutes to 1:20 minutes = 3:05 hours, which is > > wrong.) When I have a lot of entries (some are in the hundreds), the above > > formula is completely wrong. > > > > I can get close to the correct number with the formula shown below, but it > > is no longer in time format and I am questioned why something is out of > > service for 27.08 hours when everything is always in five minute increments. > > > > =Sum(DateDiff("n",[StartTime],[EndTime])/60) > > Format: Fixed > > Decimal Places: 2 > > (The same 25 entries ranging from :45 minutes to 1:20 minutes = 27:08 hours, > > is actually 27:05 hours). > > > > What is the proper way to calculate this so that I get the true time? > > > > Thank you. > > > . >
From: John Spencer on 18 Mar 2010 20:43 Where did FIRST come from? As far as I know you cannot combine aggregate functions in one query. If you are doing this in query design view, try using EXPRESSION as the choice in the Total box under the expression above. Otherwise, post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County PolQueen wrote: > Thank you, John. > > When I try to use: > Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & > Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") > > I get this message: Syntax error in query expression > 'First([Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & > Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")])' > > Should I be doing something else? > > Thank you. > > > > "John Spencer" wrote: > >> Sum(DateDiff("n",[StartTime],[EndTime]) gives you the number of minutes >> >> Sum(DateDiff("n",[StartTime],[EndTime])\60 gives you the number of hours (that >> is integer division so it removes the fractional portion). >> >> Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60 gives you the number of >> leftover minutes. >> >> So for display purposes in hours and minutes: >> Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & >> Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") >> >> 27.08 is 27 hours and 5 minutes (actually the number is probably >> 27.08333333333 but your 2 decimal places is rounding that off). >> >> Your problem with the first solution is you are attempting to show a duration >> of time using a point in time. 27 hours and 5 minutes. A point in time is >> never going to exceed 24 hours. Once it does, you are going to end up with a >> day (date) plus the time left over after you divide by 24 hours. >> >> John Spencer >> Access MVP 2002-2005, 2007-2010 >> The Hilltop Institute >> University of Maryland Baltimore County >> >> PolQueen wrote: >>> I know that working with time in Access can be tricky. I have multiple >>> locations that I am tracking times that vehicles are covering for another >>> vehicle, and I really need to use time format. When I have only a few >>> entries, the first formula works with no problem, but with a lot of entries, >>> it is incorrect: >>> >>> =Sum([StartTime]-[ EndTime]) >>> Format: Short Time >>> Input Mask: 00:00;0;_ >>> (25 entries ranging from :45 minutes to 1:20 minutes = 3:05 hours, which is >>> wrong.) When I have a lot of entries (some are in the hundreds), the above >>> formula is completely wrong. >>> >>> I can get close to the correct number with the formula shown below, but it >>> is no longer in time format and I am questioned why something is out of >>> service for 27.08 hours when everything is always in five minute increments. >>> >>> =Sum(DateDiff("n",[StartTime],[EndTime])/60) >>> Format: Fixed >>> Decimal Places: 2 >>> (The same 25 entries ranging from :45 minutes to 1:20 minutes = 27:08 hours, >>> is actually 27:05 hours). >>> >>> What is the proper way to calculate this so that I get the true time? >>> >>> Thank you. >>> >> . >>
From: PolQueen on 23 Mar 2010 15:05 John, I have no idea where the "FIRST" came from - it was in the pop up syntax error message box. I used your formula exactly (adding an = sign in front when pasting it into the control source field). I have never done a formula like that with the format in it – is that the problem? Should I be doing something else? Here is what the fields look like: CONTROL SOURCE =Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") INPUT MASK 00:00;0;_ FORMAT Fixed, DECIMALS 2 As for using the query to do this, I don't know SQL that well, and since I am summing by group and then the master report total, I don't have those types of fields in the query, so I don't know how to do that, and then make them show in the report properly. I have always used regular numbers when doing this, not time, and have never had this issue before. Thank you for your help! "John Spencer" wrote: > Where did FIRST come from? As far as I know you cannot combine aggregate > functions in one query. > > If you are doing this in query design view, try using EXPRESSION as the choice > in the Total box under the expression above. > > Otherwise, post the SQL of your query. > > (Possibly unneeded instructions follow) > Open the query > Select View:Sql from the Menu > Select all the text > Copy it > Paste it into the message > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > PolQueen wrote: > > Thank you, John. > > > > When I try to use: > > Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & > > Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") > > > > I get this message: Syntax error in query expression > > 'First([Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & > > Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")])' > > > > Should I be doing something else? > > > > Thank you. > > > > > > > > "John Spencer" wrote: > > > >> Sum(DateDiff("n",[StartTime],[EndTime]) gives you the number of minutes > >> > >> Sum(DateDiff("n",[StartTime],[EndTime])\60 gives you the number of hours (that > >> is integer division so it removes the fractional portion). > >> > >> Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60 gives you the number of > >> leftover minutes. > >> > >> So for display purposes in hours and minutes: > >> Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" & > >> Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00") > >> > >> 27.08 is 27 hours and 5 minutes (actually the number is probably > >> 27.08333333333 but your 2 decimal places is rounding that off). > >> > >> Your problem with the first solution is you are attempting to show a duration > >> of time using a point in time. 27 hours and 5 minutes. A point in time is > >> never going to exceed 24 hours. Once it does, you are going to end up with a > >> day (date) plus the time left over after you divide by 24 hours. > >> > >> John Spencer > >> Access MVP 2002-2005, 2007-2010 > >> The Hilltop Institute > >> University of Maryland Baltimore County > >> > >> PolQueen wrote: > >>> I know that working with time in Access can be tricky. I have multiple > >>> locations that I am tracking times that vehicles are covering for another > >>> vehicle, and I really need to use time format. When I have only a few > >>> entries, the first formula works with no problem, but with a lot of entries, > >>> it is incorrect: > >>> > >>> =Sum([StartTime]-[ EndTime]) > >>> Format: Short Time > >>> Input Mask: 00:00;0;_ > >>> (25 entries ranging from :45 minutes to 1:20 minutes = 3:05 hours, which is > >>> wrong.) When I have a lot of entries (some are in the hundreds), the above > >>> formula is completely wrong. > >>> > >>> I can get close to the correct number with the formula shown below, but it > >>> is no longer in time format and I am questioned why something is out of > >>> service for 27.08 hours when everything is always in five minute increments. > >>> > >>> =Sum(DateDiff("n",[StartTime],[EndTime])/60) > >>> Format: Fixed > >>> Decimal Places: 2 > >>> (The same 25 entries ranging from :45 minutes to 1:20 minutes = 27:08 hours, > >>> is actually 27:05 hours). > >>> > >>> What is the proper way to calculate this so that I get the true time? > >>> > >>> Thank you. > >>> > >> . > >> > . >
|
Next
|
Last
Pages: 1 2 Prev: Line Break in text field in a form Next: Multiple detail/record per break to reduce pages |