Query for the previous month (1 Viewer)

[dschudel]

I would like to create a query with no prompts that searches within the previous month. For instance if I were to run this report today (6/13/02) the date criteria for the query would be [start date] -> 5/1/02 and [end date] -> 5/31/02. The reason that I want to do this is so that whenever I run a report it always gives me the data relevant to the previous month. If I can get that to work then I would be able to set reports to run on the first of the month for the previous month with no manual intervention required.

 

[simongallop]

Use Dateadd. So for the start date: =Format("01/" & Format(DateAdd("m",-1,Date()),"mm/yy"),"dd/mm/yy")

end date: =Format("01/" & format(date(),"mm/yy") -1,"dd/mm/yy")

Would make one suggestion though. If you need to rerun a report from 2 months previously then you will have to go through your queries to change the formula. The way to get around it is to have a form (Enter Date) with a textbox called CurMonth and in the OnOpen event of the form have the following code:

Private Sub Form_Open(Cancel As Integer)

Forms![Enter Date]![CurMonth] = DateValue("01/" & Format(DateAdd("m", -1, Date), "mm/yy"))

End Sub

Then in your auto code run a function that opens the form then outputs your report.

This means that to manualy force another date is easier as you just need to open the form, enter the new date and run your reports.

HTH