Search by Defined Month

[Guest]

Hey guys,

How would I search by a defined month. So, for instance, I have trade data
for 12 months. I want to be able to perform a report by month, that shows
only the current month. I know the search that would allow the recipient to
access the date range in which they are looking for. That's not what I'm
looking for. I'm looking for a way to code a report to only pull May's trade
information. What is the criteria that I would put into the report?

Also, is there a place that I could go to get a list of general formula's or
criteria expressions?

 

[gls858]

Hey guys,

How would I search by a defined month. So, for instance, I have trade data
for 12 months. I want to be able to perform a report by month, that shows
only the current month. I know the search that would allow the recipient to
access the date range in which they are looking for. That's not what I'm
looking for. I'm looking for a way to code a report to only pull May's trade
information. What is the criteria that I would put into the report?

Also, is there a place that I could go to get a list of general formula's or
criteria expressions?

Build a query containing the fields you want on the report.
In the criteria for the date field use the following

Between [Enter beginning Date] And [Enter Ending Date]

Build your report based on this query. This will prompt you for a
date range each time you run the report allowing you to use the same
report for ANY month.

gls858

 

[Guest]

To return data for the current month the query would be restricted like so:

WHERE YEAR([TransactionDate]) = YEAR(DATE())
AND MONTH([TransactionDate]) = MONTH(DATE())

To allow users to specify a month use parameters in place of the DATE()
function, e.g.
[Enter year:] and [Enter month as a number from 1 to 12:]

If the table includes data for one calendar year only you can exclude the
restriction by year of course.

Ken Sheridan
Stafford, England

 

[Steve]

Create a query that includes your trade information. Add this field to your
query:
=Month(TradeDate)
and set the criteria to
Forms!NameOfFormThatLaunchesReport!ReportMonth
On your form that launches your report, add a combobox named ReportMonth and
make its rowsource a value list of all twelve months:
"January","February","March",etc
Set up your code so you have to select a month before you open the report.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Hey Ken,

Thanks for the response. Where exactly do I put the:

"WHERE YEAR([TransactionDate]) = YEAR(DATE())
AND MONTH([TransactionDate]) = MONTH(DATE())

That's a bit confusing to me. On my report I have a Date field, but would I
have to create a new field forthe two you listed above?

Andre Adams

 

[Guest]

It goes in the query on which the report is based, not in the report; that
remains unaltered.

Open the query in design view, then switch to SQL view and add the WHERE
clause, changing the field name from TransactionDate to your own field's name.

Alternatively in query design view put YEAR([TransactionDate]) in the
'field' row of a new column and YEAR(DATE()) ( or the parameter if you are
using one) in the 'criteria row of the column. Uncheck the 'show' checkbox.
Do the same in another column in design view for the month.

Ken Sheridan
Stafford, England

Hey Ken,

Thanks for the response. Where exactly do I put the:

"WHERE YEAR([TransactionDate]) = YEAR(DATE())
AND MONTH([TransactionDate]) = MONTH(DATE())

That's a bit confusing to me. On my report I have a Date field, but would I
have to create a new field forthe two you listed above?

Andre Adams

Create a query that includes your trade information. Add this field to your
query:
=Month(TradeDate)
and set the criteria to
Forms!NameOfFormThatLaunchesReport!ReportMonth
On your form that launches your report, add a combobox named ReportMonth and
make its rowsource a value list of all twelve months:
"January","February","March",etc
Set up your code so you have to select a month before you open the report.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)