special addition question

[karassik]

Hello,

I was wondering if someone might help with the following problem in Access 2000:

I have an employee timesheet form containing a continuous subform containing the employee hours, date of hours etc. for the most recent 30 days.

In the footer I have a correctly working sum for the most recent days hours,=DSum("[HoursDetail]","qryHoursDetail","[HoursDate]=[maxDate]&quot. Anyone have any ideas for getting automatically updating data ,like my sum function, that will sum the most recent weeks hours. And I am looking for Monday on, not the previous seven days.

Thanks in advance,

Nishan

The way have had to do it is creating an append query actuated by the after update, but I don't think it is very efficient.

 

[Orion45]

In my project I do something like this by using the DMax Function to return the highest date.
DMax(expr, domain[, criteria])
Once you get this, which will probably be Friday's date, you can subtract four days to get the date range of Monday-Friday. Once you have this you should be able to use the DSum function again to sum the time worked in this date range. Hope this helps, though it sounds like you're already on the right track in using domain aggregate functions.

 

[karassik]

OK, so to get that most recent monday:

[MaxDate]-Weekday([MaxDate],2)

This works correctly, but when I put it in the dsum fuction as such:

=DSum("[HoursDetail]","qryHoursDetail","[HoursDate]" >[MaxDate]-Weekday([MaxDate],2))


it returns nothing. Any suggestions?

 

[Orion45]

I would try something like this to sum a range of dates:
=DSum(&quot;[HoursDetail]&quot;,&quot;qryHoursDetail&quot;,&quot;[HoursDate]&quot; <=[MaxDate] and >=([MaxDate]-4))
However, I'm not quite sure if DSum will let you do a range you may have to sum each day in that week and then add up the sums from each day. Let me know if you can get this to work.

 

[karassik]

Ya,

I think I'll have to do each weekday, then sum it.

I think that the &quot;[HoursDate]&quot;>[MaxDate]-Weekday([MaxDate],2), works better because I don't want five previous days, I want Monday on to MaxDate.