date fields access

[sd]

I have a report that is based off a query that has a misc column with [Enter
Month Number] so I can easily hit "9" and it brings up all September products
sold. The report however has 2 columns with different products by date with
summaries. It is pulling September 2007 as well as 2008. In an easy way,
can I just have where it pulls dates for "9" 2008?". Or is the other date
columns confusing the issue....
They only want to see "total summaries" by this current months date", yet I
have 3 date fields and they can range from 2007 to 2008. Help..

 

[KARL DEWEY]

In your query you could add a calculated field for year-month ---
YearMon: Format([YourDateField],"yyyymm")
and have criteria of ---

= Format(DateAdd("m", -11, Date()), "yyyymm")

this way it will not pull anything older than 11 months ago.
--
KARL DEWEY
Build a little - Test a little

I have a report that is based off a query that has a misc column with [Enter
Month Number] so I can easily hit "9" and it brings up all September products
sold. The report however has 2 columns with different products by date with
summaries. It is pulling September 2007 as well as 2008. In an easy way,
can I just have where it pulls dates for "9" 2008?". Or is the other date
columns confusing the issue....
They only want to see "total summaries" by this current months date", yet I
have 3 date fields and they can range from 2007 to 2008. Help..

 

[Ken Sheridan]

Restricting it to the month in the current year might not work every time; if
you run the query in January to get December's sales for instance. You can
restrict the rows returned to the month in whatever is the latest year in the
database containing rows for the month in question. You'd do this by means
of a subquery like so:

SELECT *
FROM [YourTable]
WHERE MONTH([YourDateField]) = [Enter Month Number]
AND YEAR([YourDateField]) =
(SELECT MAX(YEAR([YourDateField]))
FROM [YourTable]
WHERE MONTH([YourDateField]) = [Enter Month Number]);

The only situation in which this wouldn’t work would be if you'd made no
sales whatsoever in the last occurrence of the month in question. So if you
enter 12 in January 2009, but had not sold anything in December 2008, then
you'd get the data for December 2007. I'm assuming that even in these times
of recession business isn't going to be that bad!

Ken Sheridan
Stafford, England