Selecting last months data

[Crawforda]

I am trying to create a report totalling up data for 'last month's' data input. I would like to be able to run this report at any time in the month without affecting the data collected (initially I tried using DataAdd, but found that it only collected data a month prior to the current date).
I have created a query and I'm having problems with the criteria section of the Date field.

This is the criteria I am using: Month(Now())-1

This works fine for all months apart from January, so bearing this in mind I created the If statement
IIf(Month(Now())-1=0, x ,Month(Now())-1)
The question is what to let x become in order to roll the year and month back to December 2001 (in this case).

Is there an easy way to collect 'last month's' data without any impact on when the query is run?

Any help would be greatly appreciated.

Anthony

 

[David R]

Anthony, what I would do is put this into a blank field in the base query: LastMonth: DateAdd("m",-1,Now()). I can get a date for a month ago from this w/o affecting the actual table data. Enclosing the whole thing in Month() will give you just the 12 currently, since it is January (how handy).

For comparison purposes, you could draw out the Month() and Year() of both the record date and the above, and show data for when they are equal. There's probably an easier way, but this should get you up and running.

Also look at the DatePart examples on Access help.

HTH,
David R


[This message has been edited by David R (edited 01-03-2002).]

 

[simongallop]

Similar way is to create a query which shows all the fields from the table that you want in your report.

Create a new field which will filter the data. As it does not need to be visible you can use the following (but remember to change the MyDate to whatever the date field is called in your db)

Test:Format(MyDate,"mm/yy")

Then in the criteria section of the Test field type in:

Format(DateAdd('m',-1,Now()),"mm/yy")

Ought to work!!

HTH

 

[Crawforda]

Excellent.

Cheers for both those pointers, I'll try them out as soon as I can (Unfortunantely I can only devote an hour or so a day to Access).

Again, thanks for all your help.

Anthony

 

[Crawforda]

In the end I plumped for Harry's solution which works a treat.

So thanks again for your help.

Anthony