Question Calculation of field in specific period

[ijaz196]

I have a form containing following fields:-

(a) Leave_Code
(b) Service_No
(c) Leave_From (Leave Start Date)
(d) Leave_To (Leave End Date)
(e) No_of_Days
(f) Kind_of_Leave
(g) year_of_Leave
(h)Total_C_Leave
(j) Total_A_Leave
(k) Remarks

Now I want to calculate against a specific person (Service_No) which is already selected, but when I select a year I able to count total number of C_Leave / A-Leave against that year. Any honorable member can help me in this regard

 

[arnelgp]

create the Year textbox as unbound.
on its AfterUpdate Event, calculate the C_Leave and A_Leave textbox:

Private Sub txtYear_AfterUpdate()A
Me.C_Leave = DSum("*","tblLeaves","LeaveTypeField='C' And ServiceNo='" & [Service_No] & "' And [year_of_Leave]=" & [year_of_leave])
Me.A_Leave = DSum("*","tblLeaves","LeaveTypeField='A' And ServiceNo='" & [Service_No] & "' And [year_of_Leave]=" & [year_of_leave])
End Sub

 

[ijaz196]

Mr arnelgp.

Thanks a lot for helping.

 

[arnelgp]

forget about making the Year textbox unbound.
you also need to use the Current Event of the Form:

Private Sub Form_Current()
If Me.NewRecord = False Then
Call txtYear_AfterUpdate
Me.Dirty = False
End If
End Sub

 

[Cronk]

While Arnel has provided you with an answer that will work in this instance, it would be much better if you normalized your data by splitting the leave data into a separate table.


LeaveID
ServiceNo

YearOfLeave
LeaveStart
LeaveFinish
TypeOfLeave


Incidentally, for leave that starts in December and finishes in January, what year applies?


A well constructed database would not normally assign a year; instead, calculating the number of days till the year end, or from the year start where leave straddled 2 years.