434,985 Members | 2,877 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

sum or Dsum?

 P: 12 i have a textbox in a form. Where i want it to calculate the total of hours for different employees in different month. when i use "=sum([hour])" it gives the the total of all employee in all of the monthes. and when i use Dsum as " = DSum( "[Hour]", "[tablename]", "[MonthID]" =1)", it gives me the total of all employee in January. my question is, what should i do to make it only calculate the total hour of only the one specific employee and one specific month. Feb 4 '08 #1
3 Replies

 Expert 100+ P: 254 i have a textbox in a form. Where i want it to calculate the total of hours for different employees in different month. when i use "=sum([hour])" it gives the the total of all employee in all of the monthes. and when i use Dsum as " = DSum( "[Hour]", "[tablename]", "[MonthID]" =1)", it gives me the total of all employee in January. my question is, what should i do to make it only calculate the total hour of only the one specific employee and one specific month. = DSum( "[Hour]", "[tablename]", "[MonthID]" =1 AND "[EmpID]" =1) The criteria of the DSUM works just like a Where clause without "Where". Feb 4 '08 #2

 P: 12 hi, thanks for replying, jaxjagfan.. but, it still didn't solve my problem, i don't think i explained it clear enough... the problem is that, this mainform has a subfrom. the main form contains employeeID, and the subform is where employee can enter their own hours and monthes....and on the main from footer, i have this textbox, where i want it calculate the total hours for each month. So, if i use " empID=1" , it will only give me value for EmployeeI , not total hours to the related employee. i tried to make a query, it gives me the right answer.(where shows the diffeerent month total hours to related employee name). However, i dno't know how to make the result to show on my mainform ? Do you know hwo can i solve that? Thanks. = DSum( "[Hour]", "[tablename]", "[MonthID]" =1 AND "[EmpID]" =1) The criteria of the DSUM works just like a Where clause without "Where". Feb 4 '08 #3

 Expert 100+ P: 254 hi, thanks for replying, jaxjagfan.. but, it still didn't solve my problem, i don't think i explained it clear enough... the problem is that, this mainform has a subfrom. the main form contains employeeID, and the subform is where employee can enter their own hours and monthes....and on the main from footer, i have this textbox, where i want it calculate the total hours for each month. So, if i use " empID=1" , it will only give me value for EmployeeI , not total hours to the related employee. i tried to make a query, it gives me the right answer.(where shows the diffeerent month total hours to related employee name). However, i dno't know how to make the result to show on my mainform ? Do you know hwo can i solve that? Thanks. DSum( "[Hour]", "[tablename]", "[MonthID]" =1 AND "[EmpID]" = & Me.FormEmployeeIDControl) Should be something like that. When you sum on month just remember that is All Month 1's or all January's. Make sure you include Month Year combinations at a minumum to get correct totals. Feb 4 '08 #4 