Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-21-2019, 09:02 PM   #1
vcarrill
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
vcarrill is on a distinguished road
How to omit expired data from report

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!
Attached Images
File Type: jpg Access.JPG (65.4 KB, 7 views)

vcarrill is offline   Reply With Quote
Old 08-21-2019, 09:19 PM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,357
Thanks: 67
Thanked 2,682 Times in 2,568 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to omit expired data from report

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)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-21-2019, 09:27 PM   #3
vcarrill
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
vcarrill is on a distinguished road
Re: How to omit expired data from report

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 by vcarrill; 08-21-2019 at 11:25 PM.
vcarrill is offline   Reply With Quote
Old 08-21-2019, 09:39 PM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,357
Thanks: 67
Thanked 2,682 Times in 2,568 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to omit expired data from report

date is a reserved word for a built-in function.
change RenewalDate: DateAdd("yyyy", 1 [Attendance].[Date]).
filter on this calculated field:

>= Date
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 08-21-2019 at 09:53 PM.
arnelgp is offline   Reply With Quote
Old 08-21-2019, 11:17 PM   #5
vcarrill
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
vcarrill is on a distinguished road
Re: How to omit expired data from report

When entering the information as you gave it to me, I click "Run" and get nothing.
vcarrill is offline   Reply With Quote
Old 08-21-2019, 11:32 PM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,357
Thanks: 67
Thanked 2,682 Times in 2,568 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to omit expired data from report

without the filter, >= Date, do you see any record that might be included based on RenewalDate?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-22-2019, 12:39 AM   #7
vcarrill
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
vcarrill is on a distinguished road
Re: How to omit expired data from report

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!


vcarrill is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
You cannot Always omit “Value” Uncle Gizmo Macros 1 10-20-2017 07:03 PM
Report that lists date fields that have expired gpnhmiller Reports 19 08-28-2012 03:39 AM
Field with mixed text and numbers, need to omit text data ragribben Queries 10 03-12-2011 12:19 AM
omit data mike wild Queries 1 12-09-2003 02:36 PM
This is my LAST question! :) How to OMIT a record in a report based on a field value? fivespeedv8 Reports 1 05-16-2002 03:32 PM




All times are GMT -8. The time now is 08:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World