RandyG wrote:
that worked thanks.
Now what i just noticed, im pulling information for month end reports
that shows data from years prior to the current report month. How can i
pull the current months information and prior report information?
On Oct 19, 3:00 pm, "RandyG" <astr...@gmail.comwrote:
how do you pull the current months data without having to manually
input the date each time?
SELECT IIf(DateField BETWEEN MonthStartDate AND MonthEndDate,
DataValueField, 0) AS MonthData FROM tblX WHERE DateField BETWEEN
PeriodStartDate AND PeriodEndDate;
Any DateField value outside the month will return 0 for MonthData, so
summing all the MonthData values will give you the sum for that month
even though the query returns all the records within the period.
Replace DataValueField with a 1 if a count rather than a sum is needed.
Note that there should be no time portion stored in the date fields
when using BETWEEN, otherwise you'll need to add a day to the second
date. For a given date, say dtX, the beginning of the month containing
dtX can be found using DateSerial(Year(dtX), Month(dtX), 1) and the end
of the month containing dtX can be found using DateSerial(Year(dtX),
Month(dtX) + 1, 0).
I hope this helps you get the information you need.
James A. Fortune
CD********@FortuneJames.com
That time of year thou may'st in me behold
When yellow leaves, or none, or few, do hang
Upon those boughs which shake against the cold
Bare ruin'd choirs, where late the sweet birds sang.
-- Shakespeare, Sonnet 73