Would like to run report for "last month" without entering dates

  • Thread starter Thread starter Don M
  • Start date Start date
D

Don M

I want to run a monthly report for data from the previous
month. Currently, I must provide the "start date" and
the "end date" for the query. Is there a command such as
current month that, possibly, I can "-30" or something so
that a macro can run a query/report for the prior month??
 
Assuming you have a button to launch the report. Then you would remove the
conditions from the actual query of the report.

If the datafile is small, (only a few thousand records), then you can use
conditions that simply check the month and the year. for Example:


You button code could then go:


dim strWhere as string

strWhere = "year([DateField]) = " & year(date) & _
"and month([DateField]) = " & month(date())

docmd.OpenReport "the reprot",acViewPreview,,strWhere

However, for reasons of performance, if that file is going to be more then a
few thousand records, then we should use a full date rate conditions so that
indexing can be used.

Hence, the correct solution is to use a date range. We SHOULD use:

Dim strWhere As String
Dim dtStart As Date
Dim dtEnd As Date

dtStart = DateSerial(Year(Date), Month(Date) - 1, 1)
dtEnd = DateSerial(Year(Date), Month(Date), 0)

strWhere = BuildCriteria("[DateField", dbDate, "between " & _
dtStart & " and " & dtEnd)

DoCmd.OpenReport "your report",acViewPreview,,strwhere


In fact, you might even just build a nice report prompt form for the user.
The above use of the "sql where clause" via stWhere is real nice. It means
you can get rid of all the prompts in the query, and build the where in
code. It allows you to make some real nice prompt forms. Here is some screen
shots of using the above code:

http://www.attcanada.net/~kallal.msn/Search/index.html
 
Back
Top