i would like some help in an expression please:
In a form, adeiesfrm, having source a table adeiestbl, and in texbox, I use an expression to calclulate remaining days of employees vacation.
In my work every employee has the right of 30 days of this kind of vacation in a year, or if notspent all in one year, 30 + remaining days of last year
in the next year only.
So far, because I was working in the same year, an expression i used , was cool finding out how many days an employee has in 2011.
My expression was:
TextBox: [Vacation Days Left]
But,.. because employees do not all of them spent their (30) vacation days in one year and they have the right to claim remaining days next year, like in this year (2012), they have vacation days left:
(30-sum of vacation days of last year)+ (30-sum of days this year)..
In my table adeiestbl
[ΑΔΕΙΑID] = primarykey
[ΚΑΝΟΝΙΚΗ]= number (vacation days)
[ΕΝΑΡΞΗ] = date field (start date)
[ΕΠΩΝΥΜΟID]= EmloyeeId
This is my problem now, that year has changed, to calculate the remained vacation days of an employee in
a new textbox, called [Old Vacation Days] and to have there calculated last years vacanion days left, and still
how to tell access that in present year 2012, don't sum vacation days taken by someone, if, this years days belong
to 2011 vacation in my [Vacation Days Left] TextBox. Plus i would like next year 2013, not to have problems of all former years
summed (just remainings of 2012)..
I tried to find the remainings of empolyees vacation for former year with
and it works but next year like 2013, [Vacation Days Left] assumes that the vacation days wich concern this year's remaing are calculated as 2012 vacation wich is false because it might be 2012's vacation or remainings or 2011.
In a form, adeiesfrm, having source a table adeiestbl, and in texbox, I use an expression to calclulate remaining days of employees vacation.
In my work every employee has the right of 30 days of this kind of vacation in a year, or if notspent all in one year, 30 + remaining days of last year
in the next year only.
So far, because I was working in the same year, an expression i used , was cool finding out how many days an employee has in 2011.
My expression was:
TextBox: [Vacation Days Left]
Code:
=nz(IIf(30-nz(DSum("[ΚΑΝΟΝΙΚΗ]";"adeiestbl";"Year([ΕΝΑΡΞΗ]) = " & Year([ΕΝΑΡΞΗ]) & " And [ΑΔΕΙΑID] <= " & ([ΑΔΕΙΑID] & " And [ΕΠΩΝΥΜΟID]= " & [ΕΠΩΝΥΜΟID])))<0;"ΑΛΛΑΞΕ ΤΙΣ ΜΕΡΕΣ";30-nz(DSum("[ΚΑΝΟΝΙΚΗ]";"adeiestbl";"Year([ΕΝΑΡΞΗ]) = " & Year([ΕΝΑΡΞΗ]) & " And [ΑΔΕΙΑID] <= " & ([ΑΔΕΙΑID] & " And [ΕΠΩΝΥΜΟID]= " & [ΕΠΩΝΥΜΟID])))))But,.. because employees do not all of them spent their (30) vacation days in one year and they have the right to claim remaining days next year, like in this year (2012), they have vacation days left:
(30-sum of vacation days of last year)+ (30-sum of days this year)..
In my table adeiestbl
[ΑΔΕΙΑID] = primarykey
[ΚΑΝΟΝΙΚΗ]= number (vacation days)
[ΕΝΑΡΞΗ] = date field (start date)
[ΕΠΩΝΥΜΟID]= EmloyeeId
This is my problem now, that year has changed, to calculate the remained vacation days of an employee in
a new textbox, called [Old Vacation Days] and to have there calculated last years vacanion days left, and still
how to tell access that in present year 2012, don't sum vacation days taken by someone, if, this years days belong
to 2011 vacation in my [Vacation Days Left] TextBox. Plus i would like next year 2013, not to have problems of all former years
summed (just remainings of 2012)..
I tried to find the remainings of empolyees vacation for former year with
Code:
=30-(DSum("[ΚΑΝΟΝΙΚΗ]";"adeiestbl";"Year([ΕΝΑΡΞΗ]) = " & Year([ΕΝΑΡΞΗ])-1 & " And [ΑΔΕΙΑID] <= " & ([ΑΔΕΙΑID] & " And [ΕΠΩΝΥΜΟID]= " & [ΕΠΩΝΥΜΟID])))and it works but next year like 2013, [Vacation Days Left] assumes that the vacation days wich concern this year's remaing are calculated as 2012 vacation wich is false because it might be 2012's vacation or remainings or 2011.