Help to create a query which gives me data from previous year (1 Viewer)

[Dannyc989]

Hi folk,

I am trying to create a query which will return records which are from a specific month of the previous year...

To better explain:

If I have sales from 2013 and 2014 in Jan, I want a query which will show me the results from Jan in 2013 only... I'm just unsure on how to make a query do this and it's proberly rally simple and I'm over thinking it but it's driving me around the bend

But then in 2015 I will want it to show results from 2014 without having to change the expression or criteria.

Many thanks in advance

Daniel

 

[Uncle Gizmo]

Look at the DatePart function

 

[Dannyc989]

I'm looking at it but I don't think it's going to allow me to have it automatically look at the previous year figures

 

[Uncle Gizmo]

I'm looking at it but I don't think it's going to allow me to have it automatically look at the previous year figures

Use it to extract the "yyyy" part of the date and use that in a query.

 

[Galaxiom]

Use it to extract the "yyyy" part of the date and use that in a query.

This is an often suggested solution but it is far from optimum because it involves applying a function to every record in the table before the selection criteria can be applied.

A far superior technique uses a date range and allows selection directly from the field's index.

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

This can easily be one hundred times faster. While it may not matter when there are few records it makes a huge difference as the numbers of records increase.