Solved show data from previous year (1 Viewer)

[BHWATT]

I am writing a report that needs to compare data from this year to data from last year and data from two years ago. I am struggling to figure out what to put in the date field criteria to show data between January and December of last year. I don't want to do between 01/01/2023 and 12/31/2023 because then it would need to be changed every year. I am sure it is super simple solution but google is not turning up much for me.

 

[ebs17]

WHERE DateField BETWEEN DateSerial(Year(Date()) - 1, 1, 1) AND DateSerial(Year(Date()) - 1, 12, 31)

If time components exist in the DateField and must be taken into account:

WHERE DateTimeField >= DateSerial(Year(Date()) - 1, 1, 1) 
  AND DateTimeField < DateSerial(Year(Date()), 1, 1)

 

[XPS35]

Or

WHERE YEAR(YourDateField) = YEAR(Date()) - 1

 

[Galaxiom]

WHERE YEAR(YourDateField) = YEAR(Date()) - 1

NO! That suggestion is absolutely wrong.

This expression requires a function to be run on every record before matches can be returned. Hence it cannot use the index on the date field.The difference in performance won't be noticed on small numbers of records but will become evident as the number of records grows.

Research and understand the meaning of SARGable.

 

[arnelgp]

NO! That suggestion is absolutely wrong.

I don't think that he will be Updating any records?

 

[ebs17]

@Galaxiom sees that exactly right.

@XPS35 presented a solution that is often and "commonly" used. It does what it's supposed to.

However, it is good form to immediately use solutions that not only work, but that WORK WELL - unless other needs require this.
Working well here means the best possible performance through the use of an index that may exist.
Who deliberately and unnecessarily gives away and avoids possible performance?

Therefore, my suggestion contains what should come to mind FIRST (habit formation).

 

[Gasman]

the use of an index that may exist.

The actual question would lead me to think not?

 

[Galaxiom]

I don't think that he will be Updating any records?

So?

 

[arnelgp]

So?

so what?!

 

[Galaxiom]

so what?

The important matter of whether a query is SARGable or not has absolutely nothing to do with whether a field is going to be updated or not.

Your comment ended in a question mark. I was asking what your question was. Did you actually have a question or was it meant as pointless rhetoric?

 

[CJ_London]

I'm not sure the OP has clarified the actual requirement, or perhaps I've not had enough coffee this morning

compare data from this year to data from last year and data from two years ago

I don't want to do between 01/01/2023 and 12/31/2023

So this year we are in January a few days data - to be compared with the whole of last year and the whole of the year before? Maybe, but doesn't make much sense.

So by this year, does the OP mean last year and the two years prior to that? Or does it mean year to date this year and the equivalent period for the previous two years. Or maybe something else?

Either way, both solutions proposed are for just one year, not 3.

I would also think since this is for a comparison, the query would be a crosstab - for which the criteria can be designed to make use of indexing along the lines of ebs17's suggestion, whilst the column headings are more a presentation issue per xps35's suggestion - i.e. a bit like WHERE v HAVING in an aggregate query.

 

[BHWATT]

WHERE DateField BETWEEN DateSerial(Year(Date()) - 1, 1, 1) AND DateSerial(Year(Date()) - 1, 12, 31)

If time components exist in the DateField and must be taken into account:

WHERE DateTimeField >= DateSerial(Year(Date()) - 1, 1, 1)
  AND DateTimeField < DateSerial(Year(Date()), 1, 1)

That is exactly what I was looking for. I knew it had to be something simple. Been driving me crazy. Thanks for your help as well as everyone else who commented!