Running Total Problem

[topdesk123]

Good morning,

I use the following function for calculating regular time and over time in time cards:

RunTot: (DSum("acttime","time card hours","[TimeCardDetailID]<=" & [TimeCardDetailID] & "And [timecardid] = " & [timecardid] & ""))

"ACTTIME" is actually time elapsed.

My problem is, when someone is entering time card information and forgets to enter times for a previous day, the running total does not adjust correctly. Example:

Time
Card
Detail
ID DateWorked WorkStart WorkFinish ActTime RunTot
1167 6/1/2005 1:00:00 PM 3:00:00 PM 2.00 9
1166 6/1/2005 6:30:00 AM 1:00:00 PM 6.50 7
1165 6/1/2005 6:00:00 AM 6:30:00 AM 0.50 0.5
1171 6/2/2005 1:00:00 PM 3:00:00 PM 2.00 18.5
1169 6/2/2005 6:30:00 AM 1:00:00 PM 6.50 16
1168 6/2/2005 6:00:00 AM 6:30:00 AM 0.50 9.5
1170 6/3/2005 6:00:00 AM 6:30:00 AM 0.50 16.5

Obviously, the last amount in the RunTot column should be 18.5.

I would really appreciate anybody's help on this - it's driving me mad!!

TIA.
topdesk

 

[PHV]

And this ?
RunTot: DSum("acttime","time card hours","(DateWorked+WorkStart)<=" & [DateWorked]+[WorkStart] & " And timecardid=" & [timecardid])


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topdesk123]

Hi PHV,

Thank you for your quick response. I get the following error message: Syntax error (missing operator)in query expression '(DateWorked+WorkStart)<=And timecardid = 75'.

I also have been trying to incorporate the dates to no avail.

 

[lespaul]

If this is your error:

'(DateWorked+WorkStart)<=And timecardid = 75'

and this is the part of the expression that it corresponds to:

","(DateWorked+WorkStart)<=" & [DateWorked]+[WorkStart] & " And timecardid=" & [timecardid])

the bolded section (your fields) appears to have no values because there is nothing there in the expression to be evaluated.


'(DateWorked+WorkStart)<=[DateWorked]+[WorkStart] And timecardid = 75'


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual