By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,828 Members | 1,862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,828 IT Pros & Developers. It's quick & easy.

Pull current months data

P: n/a
how do you pull the current months data without having to manually
input the date each time?

Oct 19 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

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.Datefield)=Month(Date()) And
Year(tbl.DateField)=Year(Date())

Oct 19 '06 #2

P: n/a
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?
Oct 19 '06 #3

P: n/a

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.

Oct 20 '06 #4

P: n/a
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

Oct 20 '06 #5

P: n/a
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
Oct 20 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.