CREATING A REPORT TO GATHER DATA FROM CURRENT OR LAST MONTH ENTRIE

  • Thread starter Thread starter Pitlochry1
  • Start date Start date
P

Pitlochry1

Is there anyway to create a report/query that gathers data from current or
last full calendar month of entries. E.g. without having to go into design
view every time and manually overtype the date field with >=#02/01/2009# And
<=#02/28/2009#
or having to create a query/report for every calendar month for the next
five years?
 
Pitlochry1 said:
Is there anyway to create a report/query that gathers data from current or
last full calendar month of entries. E.g. without having to go into design
view every time and manually overtype the date field with >=#02/01/2009# And
<=#02/28/2009#
or having to create a query/report for every calendar month for the next
five years?
Click to expand...

A very easy way is to use a "parameter" query. You specify a criterion
expression using, essentially, a field name your database doesn't
recognise (e.g. [enter start date] and [enter end date]). Access will
pop up a dialogue inviting you to supply values. This is, however,
better driven from a form, so you can apply some validation (to make
sure it's actually a valid date). Provide two "date" fields, put
validation code (use the IsDate function) in the "lost focus" events and
provide a "run" button. Have the query pick up the date values from the
(validated) controls on the form.

If you know you only want This Month or Last Month, you can still use a
form, but use the various Date arithmetic functions (play with the
Expression Builder!) to build a criterion in the query. The month()
function, now() function and DateAdd() functions will all be helpful here.

Phil, London
 
Philip said:
Pitlochry1 said:
Is there anyway to create a report/query that gathers data from
current or last full calendar month of entries. E.g. without having
to go into design view every time and manually overtype the date field
with >=#02/01/2009# And <=#02/28/2009#
or having to create a query/report for every calendar month for the
next five years?
Click to expand...

A very easy way is to use a "parameter" query. You specify a criterion
expression using, essentially, a field name your database doesn't
recognise (e.g. [enter start date] and [enter end date]). Access will
pop up a dialogue inviting you to supply values. This is, however,
better driven from a form, so you can apply some validation (to make
sure it's actually a valid date). Provide two "date" fields, put
validation code (use the IsDate function) in the "lost focus" events and
provide a "run" button. Have the query pick up the date values from the
(validated) controls on the form.

If you know you only want This Month or Last Month, you can still use a
form, but use the various Date arithmetic functions (play with the
Expression Builder!) to build a criterion in the query. The month()
function, now() function and DateAdd() functions will all be helpful here.

Phil, London
Click to expand...

Clarification: in my second alternative, using the various Date
function, you can get by without a form.

Phil
 
Back
Top