How to omit expired data from report (1 Viewer)

vcarrill

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 22, 2019
Messages
60
Hello,

Can someone please tell me what criteria I need to enter to omit expired data from the report?

Example: When an employee is late, leaves early, or is absent they receive either a .5 or 1 point. This point cancels after 1 year. Currently the report captures all of the information and I even have a total value for the points.

I need to be able and run this report that only shows points that have not expired, since the ones that have are irrelevant for disciplinary tracking.

I have included a snapshot of my query.

Thank you for your assistance!:)
 

Attachments

  • Access.JPG
    Access.JPG
    65.4 KB · Views: 64

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:37
Joined
May 7, 2009
Messages
19,169
Points are cumulative from the first day of his work when he is late/early quit/absent?
you can sum all points that are in present year:
Code:
ThisYearPoint: (Select Sum(T1.Point) From Attenance As T1 
Where T1.Attendance > DateSerial(Year(Date)-1, 12, 31) And 
T.ID = Attendance.ID)
 

vcarrill

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 22, 2019
Messages
60
Points are cumulative from the first day of his work when he is late/early quit/absent?

Points are generated at the time the employee is absent and expire (1) year from the date of occurrence.

Example:

Late on 08/21/2019 Points: .5, this .5 will expire on 8/21/2020.

I run a report to see where all my employees are on points, but want to omit points that have cancelled out based on the date. I currently see points that expired 6/13/2019. How can I run it and only see points that as of today are still valid and not expired yet?

Thanks
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:37
Joined
May 7, 2009
Messages
19,169
date is a reserved word for a built-in function.
change RenewalDate: DateAdd("yyyy", 1 [Attendance].[Date]).
filter on this calculated field:

>= Date
 
Last edited:

vcarrill

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 22, 2019
Messages
60
When entering the information as you gave it to me, I click "Run" and get nothing.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:37
Joined
May 7, 2009
Messages
19,169
without the filter, >= Date, do you see any record that might be included based on RenewalDate?
 

vcarrill

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 22, 2019
Messages
60
Hello,

I left my original formula in the query and for criteria I entered >=Now() and it gave the results I needed.

Thank you for trying to help me!
 

Users who are viewing this thread

Top Bottom