Solved Suggestions on a more automated year criteria?

[gojets1721]

I have a DB of complaints dating back 10 years, and I have a query specifically meant to filter to complaints from only the last three calendar years (i.e. 2022, 2021, and 2020).

Right now, I do that by creating a year column using the complaint date and then placing the following into the criteria:

In("2022","2021","2020")

I'd like to see if there's a better criteria to be using, since I'll need to manually update these years at the beginning of each year. I've used Year([SalesDate]) = Year(Date()) - 1 before, for instance, to zero in on only last years entries, but I haven't found something for the past 3 years.

Any suggestions?

 

[plog]

I assume you have no future complaints--nothing from 2023. If that's the case this will work:

Year([YourField]) >= (Year(Date())-2)

 

[moke123]

how about something like

> dateserial(year(date)-2,1,1)

 

[The_Doc_Man]

Are the database records of these complaints based on date variables or dates stored as strings?

Normally, what I would do is create a date variable, then load it with Year( Now() ) - which would give me 2022. Then subtract 2. Then create a date like

strX = "#01-Jan-" & CStr( Year( Now() ) - 2 ) & "#"

Then you can concatenate that into an SQL string based on Year( Now() ) >= strX

or something similar to that.

 

[jdraw]

It might be helpful if you could show us any table structures or queries or forms involved.
Is there some issue eg. timing/speed at the root of your question?

 

[gojets1721]

I assume you have no future complaints--nothing from 2023. If that's the case this will work:

Year([YourField]) >= (Year(Date())-2)

This worked! Thanks so much!

 

[Galaxiom]

I assume you have no future complaints--nothing from 2023. If that's the case this will work:

Year([YourField]) >= (Year(Date())-2)

Not a good query at all because it relies on applying a function to every record before performing the select.

moke123 shows the correct way to structure the WHERE clause. This is a Sargable query that can use the index on the date field.

how about something like

> dateserial(year(date)-2,1,1)