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