Query to show the last 12 months of data

[mattP]

I currently have a query that looks at the "date entered" and then returns the data for the last 15 days.

What Formula do I need to put in to capture the last 12 months worth of data ?
Basically if the query is run anytime in February it would show the data from January back 12 months ?

Any help, as always, is greatly appreciated.

MattP

 

[FoFa]

Just use DATEADD to calculate date backwards, then use that in your query.

 

[ColinEssex]

or alternatively

'Gives the date 12 months ago (01/02/04)

DateSerial(Year(YourDate), Month(YourDate) - 12, 1)

' Gives the last day of prev month(31/01/05)

DateSerial(Year(YourDate), Month(YourDate), 0)

Col

 

[mattP]

Guys,

thanks for the quick responses,

ColinEssex I assume your formula would be in the criteria for the date entered field within the query?

Again thanks for your help.

MattP

 

[ColinEssex]

The way I use it is on a button that runs the query / report. There are 2 fields, a StartDate and an EndDate that the user fills in. Next to them are the 2 hidden fields.

The code I gave you populates those hidden fields first when the button is clicked. BTW, I use it to only give the previous one months data but have amended it to give you the prev 12 months.

Then reference the hidden fields from the query, the result of which I use in a SubReport.

Col

 

[mattP]

ColinEssex,

Ok many thanks, I'm sure I can get this to work in the query criteria, my query is not run via the DB, it is accessed via an Excel report, that extracts the query data and creates several pivots and charts.

I'll give it a go.

Thanks

MattP