Pull current months data

[RandyG]

how do you pull the current months data without having to manually
input the date each time?

[pietlinden]

RandyG wrote:

how do you pull the current months data without having to manually
input the date each time?

Something like

SELECT...
FROM ...
WHERE Month(tbl.Datef ield)=Month(Dat e()) And
Year(tbl.DateFi eld)=Year(Date( ))

[RandyG]

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?

[pietlinden]

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?

I don't understand. If you have a filter for Year and for Month you
should be OK. post your SQL.

[CDMAPoster]

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********@Fort uneJames.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

[Rick Brandt]

RandyG wrote:

how do you pull the current months data without having to manually
input the date each time?

This would be more efficient than using Month() if your date field is indexed.

SELECT *
FROM TableName
WHERE DateField >= DateSerial(Year (Date()), Month(Date()), 1)
AND DateField < DateSerial(Year (Date()), Month(Date()) + 1, 0)
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com