Display records from previous month

[Guest]

I am trying to create a report that uses records from the previous month. The
approach I am attempting is to base the report on a query that has been built
to return the records for the previous month. The field name containing the
date is Due Date. In the query, I have used DateAdd("m",-1,Date()) as my
criteria under the "Due Date" field, but it returns nothing, even though
there are two records that should be displayed. So, my questions are:

1) Am I using an incorrect formula? I have tried a few combinations of
DateSerial functions, to no success. What would the correct formula be?
2) Am I taking the wrong approach? Should I build that filter directly into
the report?

Thanks,

JL

 

[Guest]

Try this instead if you want to dispaly all the records from the prev month

Select * From TableName Where Month(DateField) = DateAdd("m",-1,Date())

 

[John Spencer]

Your formula would generate October 17th for today's date of Nov 17th. I
would try.

Field: Due Date
Criteria: Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),0)

If your Due Date field contains a time, then you may need to change that to
Criteria: >=DateSerial(Year(Date()),Month(Date())-1,1) And
<DateSerial(Year(Date()), Month(Date()),1)

In SQL

SELECT ...
FROM ...
WHERE [Due Date] >=DateSerial(Year(Date()),Month(Date())-1,1) And [Due
Date] <DateSerial(Year(Date()), Month(Date()),1)

 

[Guest]

Many thanks! I used your first suggestion, and it worked perfectly.

Regards,

JL

Your formula would generate October 17th for today's date of Nov 17th. I
would try.

Field: Due Date
Criteria: Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),0)

If your Due Date field contains a time, then you may need to change that to
Criteria: >=DateSerial(Year(Date()),Month(Date())-1,1) And
<DateSerial(Year(Date()), Month(Date()),1)

In SQL

SELECT ...
FROM ...
WHERE [Due Date] >=DateSerial(Year(Date()),Month(Date())-1,1) And [Due
Date] <DateSerial(Year(Date()), Month(Date()),1)

I am trying to create a report that uses records from the previous month.
The
approach I am attempting is to base the report on a query that has been
built
to return the records for the previous month. The field name containing
the
date is Due Date. In the query, I have used DateAdd("m",-1,Date()) as my
criteria under the "Due Date" field, but it returns nothing, even though
there are two records that should be displayed. So, my questions are:

1) Am I using an incorrect formula? I have tried a few combinations of
DateSerial functions, to no success. What would the correct formula be?
2) Am I taking the wrong approach? Should I build that filter directly
into
the report?

Thanks,

JL

 

[Guest]

I need to same thing you have done. I need to filter for previous month's
records. I entered:
Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),0).
But I got the message: the expression you entered contains invalid syntax.
Please tell me what you had to do to get this to work. Thanks.

Many thanks! I used your first suggestion, and it worked perfectly.

Regards,

JL

Your formula would generate October 17th for today's date of Nov 17th. I
would try.

Field: Due Date
Criteria: Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),0)

If your Due Date field contains a time, then you may need to change that to
Criteria: >=DateSerial(Year(Date()),Month(Date())-1,1) And
<DateSerial(Year(Date()), Month(Date()),1)

In SQL

SELECT ...
FROM ...
WHERE [Due Date] >=DateSerial(Year(Date()),Month(Date())-1,1) And [Due
Date] <DateSerial(Year(Date()), Month(Date()),1)

I am trying to create a report that uses records from the previous month.
The
approach I am attempting is to base the report on a query that has been
built
to return the records for the previous month. The field name containing
the
date is Due Date. In the query, I have used DateAdd("m",-1,Date()) as my
criteria under the "Due Date" field, but it returns nothing, even though
there are two records that should be displayed. So, my questions are:

1) Am I using an incorrect formula? I have tried a few combinations of
DateSerial functions, to no success. What would the correct formula be?
2) Am I taking the wrong approach? Should I build that filter directly
into
the report?

Thanks,

JL

 

[Rick Brandt]

I need to same thing you have done. I need to filter for previous
month's records. I entered:
Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),0).
But I got the message: the expression you entered contains invalid
syntax. Please tell me what you had to do to get this to work. Thanks.

Where did you enter this? It looks okay to me. It should be in the criteria row
under a date field. Is that how you used it?

If you can't get it post the entire SQL of your query.