Reports that filter by previous month (1 Viewer)

[thocutt]

Hi All.

I am having a problem figuring out how to filter my reports by previous month. I have a table of data and as each month comes we do a report on the data for the previous month. Every month I go into all my queries and say Like "11/*/00" under the DATE field. I know there is a way to automate this task, but I am not sure how to approach it. Does anyone have any experience with this type of problem? Any help would be much appreciated.

 

[Richie]

DateAdd("m",-1,Date())in the criteria grid

 

[thocutt]

I pasted what you replied underneath my Date Field in the criteria box, but I got zero data returned when I looked at the report even though I do have fields for November. Any guess as to why this is happening? I'm not real familiar with this yet. Thanks for the response.

 

[thocutt]

OK, I think I'm getting a bit closer. I changed a record to exactly one month ago and it showed up, so great! The only problem is I want to show all the records for that month. Any ideas?

 

[Richie]

Yes I had forgotten datediff m is a pretty naff function on it's own I had to create the following two functions
Function StartOfMonth(D As Date)As Variant
StartOfMonth = DateSerial(Year(D), Month(D) - 1, 1)
End Function

Function EndOfMonth(D As Date) As Variant
EndOfMonth = DateSerial(Year(D), Month(D) + 0, 0)

End Function
In the criteria cell for your date field add
Between StartOfMonth(Date()) And EndOfMonth(Date())
It worked fine on a table that I have with dates in.

 

[thocutt]

Oh my gosh! It worked! At first I put the functions in the report's module and it wouldn't recognize the function (because it pulls from the query?) but then when I created a module and put the functions in there it worked right off the bat! This is great. I really appreciate your help with this Richie. I would have never found that DateSerial function. One question though: Next month, when the year changes to 2001, how does Access know that we mean December 2000, not December 2001? Is that a built in property?