Add date parameter to query

  • Thread starter Thread starter Meejung
  • Start date Start date
M

Meejung

I am using a database which is used to track call types and volume. I need
to run the report at the beginning of each month for the prior month. Is
there an simple way to run a query or a report which will only give me the
information for the prior month?
 
I am using a database which is used to track call types and volume. I need
to run the report at the beginning of each month for the prior month. Is
there an simple way to run a query or a report which will only give me the
information for the prior month?
Click to expand...

Always for the previous month?

Add a new column to the query grid.
Exp:Format([DateField],"mm/yyyy")

You can uncheck the Show check box on this column.

As criteria on this column, write:
Format(DateAdd("m",-1,Date()),"mm/yyyy")

Only records of the previous month will be displayed.
 
You have been given one method to do this. Let me add another which can be
more efficient.

Field: YourDateField
Criteria: >=DateSerial(Year(Date()),Month(Date())-1,1) and
<DateSerial(Year(Date()), Month(Date()),1)

IF your date field does not contain a time component, but only a date then the
following also works well
Field: YourDateField
Criteria: Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()), Month(Date()),0)

The reason these can be more efficient is they can use any index you may have
on your date field and they don't make multiple calls to VBA functions. With
relatively small sets of data, any of the three will work well. With large
sets of data, you may notice a significant difference in timing.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I am using a database which is used to track call types and volume. I need
to run the report at the beginning of each month for the prior month. Is
there an simple way to run a query or a report which will only give me the
information for the prior month?
Click to expand...

Always for the previous month?

Add a new column to the query grid.
Exp:Format([DateField],"mm/yyyy")

You can uncheck the Show check box on this column.

As criteria on this column, write:
Format(DateAdd("m",-1,Date()),"mm/yyyy")

Only records of the previous month will be displayed.
Click to expand...
 
Back
Top