Dsum() Function

[rkalapura]

Hai,

I am using the following Dsum function to get the total personel time hours used by each employee. It works well with the selected year like 2010, 2011, 2012 etc.



DSum("[hoursused]","[annual vacasion]","[EMPLOYEEID]=" & [Forms]![Employee Details]![EmployeeID] & " And Year([startdate])=" & [Forms]![Employee Details]![YearSelect])

But in our office records, year starts from July 01, and eds at June 30 every year. So I need to change my second criteria to select record in between two dates (ie. like "between [yearstart] and [yearends]"). Is it possible to change the second criteria to between two dates in Dsum() function or any other input to sort this out? I have already these date fileds available in the form.

Appreciate your help

Thank you

[rpeare]

DSum("[hoursused]","[annual vacasion]","[EMPLOYEEID]=" & [Forms]![Employee Details]![EmployeeID] & " And [startdate] between #" & cdate("7/1/" & [Forms]![Employee Details]![YearSelect] -1) & "# and #" & cdate("6/30/" & [Forms]![Employee Details]![YearSelect]) & "#")

I haven't tested so the parentheses might be off.

Basically you can keep your same setup so let's say your FY 2012 goes from 7/1/2011 through 6/30/2012 you can keep your year selection and just perform a change to date (cdate) function based on the selected year.

[rkalapura]

Thank you very much. It works fine.

God Bless You!