Question Calculation of field in specific period (1 Viewer)

I

ijaz196

Registered User.

Local time

Today, 17:22

Joined

Dec 29, 2017

Messages

22

• Nov 5, 2018

• #1

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

..forever waiting... waiting for jellybean!

Local time

Today, 20:22

Joined

May 7, 2009

Messages

19,233

• Nov 5, 2018

• #2

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

 

I

ijaz196

Registered User.

Local time

Today, 17:22

Joined

Dec 29, 2017

Messages

22

• Nov 5, 2018

• #3

Mr arnelgp.

Thanks a lot for helping.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 20:22

Joined

May 7, 2009

Messages

19,233

• Nov 5, 2018

• #4

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

Registered User.

Local time

Today, 22:22

Joined

Jul 4, 2013

Messages

2,771

• Nov 5, 2018

• #5

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.