I am using Dsum to retrieve totals of empolyees holidays or vacation, and i have this table:
adeiestbl
Vacationid = Autonumber
SurnameId = Number
DateofRequest = Date/Time
numberofdays = Number
Starting = Date/Time
TypeofHolidays = Number
then in a form havind the above table as recoursource, i am using an expression which returns the rest of the 30 days employees have the right to spend in a year:
The employees have the right to spend 30 days of "TypeofHolidays" in a year and this expression is working if employees spend the the whole amount of their 30 days in the specific year.
But they have the right also to claim their days if they didnt spend them all in a year, in the next year untill almost Easter..
Simplier if an employee has taken 20 days of "TypeofHolidays" in 2011,
when he aplies for holidays in 2012 his whole "TypeofHolidays" amount is 10 + 30 = 40 days.
I tried with Dsum to return the remaings of 30 days of "TypeofHolidays" for Year -1 , but its kind of tricky, and within 2012 my database will not return the right values
I would like some help with Dsum or IIf because i have tried to calculate the value unsuccefully and .. i ve run off ideas
adeiestbl
Vacationid = Autonumber
SurnameId = Number
DateofRequest = Date/Time
numberofdays = Number
Starting = Date/Time
TypeofHolidays = Number
then in a form havind the above table as recoursource, i am using an expression which returns the rest of the 30 days employees have the right to spend in a year:
Code:
=nz(IIf(30-nz(DSum("[TypeofHolidays]";"adeiestbl";"Year([Starting]) = " & Year([Starting]) & " And [Vacationid] <= " & ([Vacationid] & " And [SurnameId]= " & [SurnameId])))<0;
"CHANGE THE DAYS";30-nz(DSum("[TypeofHolidays]";"adeiestbl";"Year([Starting]) = " & Year([Starting]) & " And [Vacationid] <= " & ([Vacationid] & " And [SurnameId ]= " & [SurnameId])))))The employees have the right to spend 30 days of "TypeofHolidays" in a year and this expression is working if employees spend the the whole amount of their 30 days in the specific year.
But they have the right also to claim their days if they didnt spend them all in a year, in the next year untill almost Easter..
Simplier if an employee has taken 20 days of "TypeofHolidays" in 2011,
when he aplies for holidays in 2012 his whole "TypeofHolidays" amount is 10 + 30 = 40 days.
I tried with Dsum to return the remaings of 30 days of "TypeofHolidays" for Year -1 , but its kind of tricky, and within 2012 my database will not return the right values
I would like some help with Dsum or IIf because i have tried to calculate the value unsuccefully and .. i ve run off ideas
