Scot said:
is there a way to run either a query or report that returns a series of dates
each month but rather than simply reporting what is currently in the fiels
also report the previous 12 months of information for the same fields. ex
Start Construction Current for each month Jamuary through December.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Sure there is - Set up a criteria for the "Construction Date" like this:
(SQL View)
WHERE construction_date BETWEEN #1/1/2009# And #12/31/2009#
You could also use prompt parameters like this:
PARAMETERS StartDate Date, EndDate Date; <- note the semi-colon
SELECT ...
FROM ...
WHERE construction_date BETWEEN StartDate And EndDate
Or, if you like, you could just have a calculated criteria: this one
will calculate the date for 12 months prior to the current date:
WHERE construction_date BETWEEN DateAdd("m", -11, Date()) And Date()
The DateAdd() function subtracts 11 months from the current date, which
would result in the function returning a January date if the current
date was in December.
If you wanted to use just Months as the criteria you'd do something like
this:
WHERE Format(construction_date, "yyyymm")
BETWEEN Format(StartDate, "yyyymm")
And Format(EndDate, "yyyymm")
The Format() function returns the date like this: "200812." You need
both the year and the month numbers whenever you want to see Month data
that crosses a year boundary.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBST7nUYechKqOuFEgEQJ6EgCgqSW0EqpUkOmM7nB7hfprMH1TqrkAnig/
sQOJIHMQriTcw9vKgOZLiHfq
=AjVT
-----END PGP SIGNATURE-----
