var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: accumulated time query
is this query convertible from SQL to ms access? it gives me accumulated time from int values
CONVERT(varchar, SUM(COALESCE (s2.DykTid, 0)) / 60) + ':' + RIGHT('00' + CAST(SUM(COALESCE (s2.DykTid, 0)) % 60 AS varchar(2)), 2) AS AccumulatedTime
i was given help make thι query in this thread ( www.dbforums.com/showthread.php?t=1621731) and now i need it to work in access.
thx in advance
Doesn't that work in Access already?
CONVERT and CAST both translate to STR, COALESCE converts to ISNULL, the plus sign for concatenation converts to ampersand, and the modulo operator % converts to MOD
so, george, no it doesn't
Should have noticed the plus signs, but I could have sworn convert and coalesce were supported. Apologies!
now i can't make that MOD to work
im trying this:
SELECT s1.LogNr, s1.DykTid, STR(CHAR, SUM(ISNULL(s2.DykTid, 0)) / 60) & ':' & RIGHT('00' & STR(SUM(ISNULL(s2.DykTid, 0)) MOD 60 AS CHAR(2)), 2) AS AccumulatedTime, s1.LoggID
I changed the VARCHAR to CHAR since that dosen't seems to be supported in access or am i wrong?
mrpcguy, you cannot just substitute the access function name for the sql server function name, you need also to accommodate the differences in the syntax
so for example CONVERT( datetype, expression) does not automatically become STR( datetype, expression)
Everything works fine except that MOD part of the query, i just can't figure that one out.
got this so far... and its not working at all, just gives me wrong number
Str(Sum(Str(s2.DykTid)) /60) & ":" & Right('00' & Str(Sum(Str(s2.DykTid))),2) MOD 60 AS AccumulatedTime
let's examine this: Str(Sum(Str(s2.DykTid)) /60)
functions, when nested, are executed from the inside out
so, first thing, we have Str(s2.DykTid), which converts a column value to a string
the next thing that happens is Sum(Str(s2.DykTid)) -- you're summing up all these strings, and that's not quite right, because SUM is supposed to operate on numbers, not strings
luckily for you, access is smart enough to convert your carefully crafted string back to numbers first
finally after dividing the sum by 60, you are converting it back to a string
the same type of problem occurs in the other part of the formula
try this and see if it works:
Str(Sum(s2.DykTid) /60) & ":" & Right('00' & Str(Sum(s2.DykTid) MOD 60),2) AS AccumulatedTime
thx alot for help
this Str(Sum(s2.DykTid) /60) & ":" & Right('00' & Str(Sum(s2.DykTid) MOD 60),2) AS AccumulatedTime
gives me this:
so now i use this:
left(Sum(s2.DykTid)/60,2) & ":" & Sum(s2.DykTid) Mod 60 AS AccumulatedTime, s1.LogNr, s1.DykTid, s1.LoggID
and it kinda gives me what i want except for a few things , the output looks like this:
because i use left i have the "," in shorter ´than 2 digits, the solution would be to calculate where "," is and then cut in correct place.
The other thing that i don't have any idea how to solve is the 9:1 output, it should be a zero in front of numbers lower then 10 so it should say 9:01 (9hours and 1min)
Last edited by mrpcguy; 09-06-07 at
right('0'&Sum(s2.DykTid)/60,2) & ":" &
right('0'&Sum(s2.DykTid) Mod 60,2) AS AccumulatedTime
i change your right to a left and now everything looks much better but now hour 10 become 01 and hour 20 equals 02 and so on...
What happens if you leave them as a right?
sry im only change the first right to left
with both right i get this
Are you sure you have Right(<val>,2) & ":" ?
This is what i use and what i get
right('0'&Sum(s2.DykTid)/60,2) & ":" & right('0'&Sum(s2.DykTid) Mod 60,2) AS Accu1,
left('0' & Sum(s2.DykTid)/60,2) & ":" & right('0'&Sum(s2.DykTid) Mod 60,2) AS Accu2,
(Sum(s2.DykTid)/60) & ":" & (Sum(s2.DykTid) Mod 60) AS Accu3
Accu1 Accu2 Accu3
67:01 09:01 9,01666666666667:1
67:52 09:52 9,86666666666667:52
33:47 01:47 10,7833333333333:47
33:32 01:32 11,5333333333333:32
i used 3 instead of 2 in in earlier post
this is what i want
and it should also work on 3 digits hour like 100:30