Looking for criteria in query that filters based on last month'srecord date

[AA Arens]

In the design status of the query I want to set a criteria to only
list the records from the last month. One of the form and table fields
of a record is Loggingdate (e.g. the date that a new record was set).

What is the criteria code? I monthly want to make reports.

Now it is 22 August. Sample:

12 June - not selected
14 July - selected
1 August - not selected.

Thank you.

Bart, Access 2003

 

[Arvin Meyer [MVP]]

In the criteria box of the date field use the expression:

Month([DateField])=Month(Date())

The query should look something like:

SELECT tblMyData.*
FROM tblMyData
WHERE ((Month([DateField])=Month(Date())));

Where tblMyData is your table's name, and DateField is the name of the
Loggingdate field.

 

[AA Arens]

In the criteria box of the date field use the expression:

Month([DateField])=Month(Date())

The query should look something like:

SELECT tblMyData.*
FROM tblMyData
WHERE ((Month([DateField])=Month(Date())));

Where tblMyData is your table's name, and DateField is the name of the
Loggingdate field.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

In the design status of the query I want to set a criteria to only
list the records from the last month. One of the form and table fields
of a record is Loggingdate (e.g. the date that a new record was set).

What is the criteria code? I monthly want to make reports.

Now it is 22 August. Sample:

12 June - not selected
14 July - selected
1 August - not selected.

Thank you.

Bart, Access 2003

Where do I have to place the code that you give to me? In the query
properties?

 

[John W. Vinson]

In the design status of the query I want to set a criteria to only
list the records from the last month. One of the form and table fields
of a record is Loggingdate (e.g. the date that a new record was set).

What is the criteria code? I monthly want to make reports.

Now it is 22 August. Sample:

12 June - not selected
14 July - selected
1 August - not selected.

Thank you.

Bart, Access 2003

If you want just records from the previous month (e.g. on August 1, or August
21, or August 31 you want all records from July) I'd suggest creating a Query
based on your table, and on the Criteria line under Loggingdate put

= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

This will work in January to get the previous December.

 

[AA Arens]

If you want just records from the previous month (e.g. on August 1, or August
21, or August 31 you want all records from July) I'd suggest creating a Query
based on your table, and on the Criteria line under Loggingdate put


This will work in January to get the previous December.

Thanks, worked fine. How to have the records in the report ordered by
one of the fields from the query? Choosing Ascending in the query has
no effect to the report.

 

[Allen Browne]

Use the Sorting And Grouping box in report design view.

 

[Tom Wickerath]

Hi Bart,

Reports do not honor sorts applied in queries. Instead, in report design
view, click on View | Sorting and Grouping. Specify your needed sort using
this dialog.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[AA Arens]

Hi Bart,

Reports do not honor sorts applied in queries. Instead, in report design
view, click on View | Sorting and Grouping. Specify your needed sort using
this dialog.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________

Hi, I cannot see Sorting and Grouping in the menu.

 

[Arvin Meyer [MVP]]

In the criteria box of the date field use the expression:

Month([DateField])=Month(Date())

The query should look something like:

SELECT tblMyData.*
FROM tblMyData
WHERE ((Month([DateField])=Month(Date())));

Where tblMyData is your table's name, and DateField is the name of the
Loggingdate field.

Where do I have to place the code that you give to me? In the query
properties?
-------
Use the query grid (QBE) and put the expression in the criteria box of the
Loggingdate field:

Month([Loggingdate])=Month(Date())

or paste the entire SQL statement above in an SQL window, and change the
names to match your table and field names.

 

[John Spencer]

Are you in design view?
Are you using a version of Access before Access 2007?

If so, Sorting and Grouping is in the View Menu.

If you don't see the view menu, then post back.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[AA Arens]

Are you in design view?
Are you using a version of Access before Access 2007?

If so, Sorting and Grouping is in the View Menu.

If you don't see the view menu, then post back.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

Thanks, I found it out.

 

[John W. Vinson]

Thanks, worked fine. How to have the records in the report ordered by
one of the fields from the query? Choosing Ascending in the query has
no effect to the report.

Reports (unlike Forms) blithely ignore any sort order specified in their
recordsource query. The only way to control the record order on a Report is to
do as Allen suggests - use the report's Sorting and Grouping feature (right
click the little square at the upper left in design view and choose Sorting
and Grouping, is one way to get to it).