Filter Date field for 3 years of records

[Lavenderchan]

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

Year: Max(DatePart('yyyy',[date_received]))

[/indent] and then filtering it by

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?

 

[dhookom]

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

 

[Lavenderchan]

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

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;

 

[dhookom]

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

 

[Lavenderchan]

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.

 

[dhookom]

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:

? 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

 

[Andrzejek]

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 [tt]t_ActiveProperties.property_id[/tt] and [tt]AFS_dbo_v_afs_standard_data.afs_fye[/tt]

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


---- Andy

There is a great need for a sarcasm font.