Filter for Previous Month's Records

G

Guest

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.
 
A

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.
 
T

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))
)"

Lewis said:
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.
 
G

Guest

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.


Allen Browne said:
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.

Lewis M said:
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.
 
T

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.
 
G

Guest

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.

tiger0268 via AccessMonster.com said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top