×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Filter Date field for 3 years of records

Filter Date field for 3 years of records

Filter Date field for 3 years of records

(OP)
Hello,

I have a query that I need to filter dates and return records for the last three years. I have done this by using

CODE

Year: Max(DatePart('yyyy',[date_received])) 
[/indent] and then filtering it by

CODE -->

Year(Date())-3 
However, I have relized this is not working because there can be to records submitted in the same year and now it has two records showing instead of the last one recieved. Is there a way to use date part to include the month and then use the same filter method?

RE: Filter Date field for 3 years of records

Can you show your entire SQL? I'm not sure why you are using Max(). By "last three years" I assume you are going back to January 1 of the oldest year.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Filter Date field for 3 years of records

(OP)
Here is my sql. I hope this clairfys the issue I am having.

CODE --> sql

SELECT t_ActiveProperties.property_id, Max(AFS_dbo_v_afs_standard_data.project_afs_header_id) AS MaxOfproject_afs_header_id, AFS_dbo_v_afs_standard_data.afs_fye, Max(AFS_dbo_v_afs_standard_data.date_received) AS MaxOfdate_received, Max(DatePart('yyyy',[date_received])) AS [Year], Max(DatePart('yyyy',[afs_fye])) AS FYE INTO tbltest
FROM t_ActiveProperties INNER JOIN AFS_dbo_v_afs_standard_data ON t_ActiveProperties.property_id = AFS_dbo_v_afs_standard_data.property_id
GROUP BY t_ActiveProperties.property_id, AFS_dbo_v_afs_standard_data.afs_fye
HAVING (((Max(DatePart('yyyy',[date_received])))=Year(Date())-3))
ORDER BY t_ActiveProperties.property_id; 

RE: Filter Date field for 3 years of records

Hi Lavenderchan,
I was hoping you would provide us with your specification/needs. You stated your query doesn't work because "there can be to records submitted in the same year" which doesn't really tell us your expectations.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Filter Date field for 3 years of records

(OP)
I need to filter the query by month and year. However, I have only been able to do it by using datpart with year. Is there a way to extract month and year from date received and filter it out for the last 3 years of records. Any other date function I have tried on date received has not worked.

RE: Filter Date field for 3 years of records

There are lots of date functions to return year, month, and other date parts.
You can open the debug window (press Ctrl+G) to test these like:

CODE --> immediate

? Format(Date(),"yyyymm")
? Format(DateAdd("yyyy",-3,Date()),"yyyymm")
? Year(Date())*100 + Month(date())
? (Year(Date())-3)*100 + Month(date()) 

I don't know how this would resolve your issue "there can be to records submitted in the same year".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Filter Date field for 3 years of records

"I need to filter the query by month and year" based on which field in your query?
The only 2 fields that you use 'straight' are t_ActiveProperties.property_id and AFS_dbo_v_afs_standard_data.afs_fye

If afs_fye is a DATE field, you can filter on it "by month and year".
(Because I don't think property_id is a DATE field)


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close