Prev: Access 2003 cannot open .mde made by Access 2007?
Next: query datasheet view on data access page?
From: Meejung on 18 Jun 2008 17:59
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?
From: fredg on 18 Jun 2008 19:51
On Wed, 18 Jun 2008 14:59:01 -0700, Meejung wrote:

> 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?

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.



--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
From: Damon Heron on 18 Jun 2008 20:08
To get the prior month, use dateadd:
yourpriormonth = dateadd("m",-1, date)

Damon



"Meejung" <Meejung(a)discussions.microsoft.com> wrote in message
news:37E3613B-CE9E-4E48-83AC-EFD51399A187(a)microsoft.com...
>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?


From: John Spencer on 19 Jun 2008 07:50
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

fredg wrote:
> On Wed, 18 Jun 2008 14:59:01 -0700, Meejung wrote:
>
>> 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?
>
> 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.
>
>
>
 | 
Pages: 1
Prev: Access 2003 cannot open .mde made by Access 2007?
Next: query datasheet view on data access page?