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

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

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
 
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.
 
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).
Click to expand...
What is the criteria code? I monthly want to make reports.
Click to expand...
Now it is 22 August. Sample:
Click to expand...
12 June - not selected
14 July - selected
1 August - not selected.
Click to expand...
Thank you.
Click to expand...
Bart, Access 2003
Click to expand...
Click to expand...

Where do I have to place the code that you give to me? In the query
properties?
 
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
Click to expand...

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)
Click to expand...

This will work in January to get the previous December.
 
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.
Click to expand...

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.
 
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/
__________________________________________
 
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/
__________________________________________
Click to expand...

Hi, I cannot see Sorting and Grouping in the menu.
 
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.
Click to expand...

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.
 
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
'====================================================
 
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
'====================================================
Click to expand...

Thanks, I found it out.
 
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.
Click to expand...

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).
 
Back
Top