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

[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?

 

[Philip Herlihy]

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?

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

 

[Allen Browne]

Criteria for last month:

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

AND < Date() - Day(Date()) + 1

Criteria for this month:

= Date() - Day(Date()) + 1 AND

< DateSerial(Year(Date()), Month(Date()) + 1, 1)

 

[Philip Herlihy]

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?

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

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

Phil