Filter for Previous Month's Records

[Lewis M]

In a query and in a report, I tried to use two different criteria as a filter
for previous month's records.

Filter 1:
Year([FieldName]) = Year(Now()) And Month([FieldName]) = Month(Now()) -1

Filter 2:
Between DateSerial(Year(Date()), Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)

But in both cases, no records were returned although there are records from
the previous month. I am using Access 2003 with SP2. Do you know if I should
use a different filter or correct the one I am trying to use? Thanks.

 

[Allen Browne]

Filter 2 should work.
(Filter 1 will probably fail in January, and be slower.)

In what context are you trying to use this filter:
- In the Criteria row in a query? Under what type of field?

- In the Filter of a form? Did you inclulde a field name?

- In the WhereCondition of OpenReport? Ditto.

 

[tiger0268 via AccessMonster.com]

Try this:

Me.filter = "(((TableName.DateFieldName)>DateSerial(Year(Date()),Month(Date())
-1,0) And (TableName.DateFieldName)<=DateSerial(Year(Date()),Month(Date()),0))
)"

In a query and in a report, I tried to use two different criteria as a filter
for previous month's records.

Filter 1:
Year([FieldName]) = Year(Now()) And Month([FieldName]) = Month(Now()) -1

Filter 2:
Between DateSerial(Year(Date()), Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)

But in both cases, no records were returned although there are records from
the previous month. I am using Access 2003 with SP2. Do you know if I should
use a different filter or correct the one I am trying to use? Thanks.

 

[Lewis M]

I tried this filter in two places. The first was in a query on the criteria
row under a date/time field. The second was in a report on the report's
filter property.

Filter 2 should work.
(Filter 1 will probably fail in January, and be slower.)

In what context are you trying to use this filter:
- In the Criteria row in a query? Under what type of field?

- In the Filter of a form? Did you inclulde a field name?

- In the WhereCondition of OpenReport? Ditto.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In a query and in a report, I tried to use two different criteria as a
filter
for previous month's records.

Filter 1:
Year([FieldName]) = Year(Now()) And Month([FieldName]) = Month(Now()) -1

Filter 2:
Between DateSerial(Year(Date()), Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)

But in both cases, no records were returned although there are records
from
the previous month. I am using Access 2003 with SP2. Do you know if I
should
use a different filter or correct the one I am trying to use? Thanks.

 

[tiger0268 via AccessMonster.com]

Sorry, I seen "filter" and I thought form. Just put the following into the
criteria section of the date field:

DateSerial(Year(Date()),Month(Date())-1,0) And <=DateSerial(Year(Date()),Month(Date()),0)

Try this:

Me.filter = "(((TableName.DateFieldName)>DateSerial(Year(Date()),Month(Date())
-1,0) And (TableName.DateFieldName)<=DateSerial(Year(Date()),Month(Date()),0))
)"

In a query and in a report, I tried to use two different criteria as a filter
for previous month's records.

[quoted text clipped - 9 lines]

the previous month. I am using Access 2003 with SP2. Do you know if I should
use a different filter or correct the one I am trying to use? Thanks.

 

[Lewis M]

Thanks for the help. I tried my filter 2 again and it worked. I recently
installed Office 2003 SP2, but I am not sure if this made the difference.

Sorry, I seen "filter" and I thought form. Just put the following into the
criteria section of the date field:

DateSerial(Year(Date()),Month(Date())-1,0) And <=DateSerial(Year(Date()),Month(Date()),0)

Try this:

Me.filter = "(((TableName.DateFieldName)>DateSerial(Year(Date()),Month(Date())
-1,0) And (TableName.DateFieldName)<=DateSerial(Year(Date()),Month(Date()),0))
)"

In a query and in a report, I tried to use two different criteria as a filter
for previous month's records.

[quoted text clipped - 9 lines]

the previous month. I am using Access 2003 with SP2. Do you know if I should
use a different filter or correct the one I am trying to use? Thanks.