Hi TQ,
If you only wish to select records for the year 2006 only, the easiest way
would be to hard code the date range: Between #1/1/2006 AND 12/31/2006#
(using the US date format).
I suspect that what you really want is a "self-adjusting" crosstab query
that picks up records for the current year, regardless of which year it is.
You can use a WHERE condition that looks like this:
WHERE (((DATE_RECEIVED) Between Date() And "1/1/" & Year(Date())))
Here is crosstab query that you can try in the Northwind sample database. To
test this query, set your system date to December 31, 1996, so that it will
pick up all orders made in the year 1996. Then try running it after advancing
your date by one year, to December 31, 1997, and then to December 31, 1998:
TRANSFORM
Sum(CCur((1-[Discount])*([Order Details.UnitPrice]*[Quantity]))) AS Sales
SELECT [Lastname] & ", " & [FirstName] AS Employee
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID)
INNER JOIN (Employees INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID) ON Employees.EmployeeID =
Orders.EmployeeID)
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between Date() And "1/1/" & Year(Date())))
GROUP BY [Lastname] & ", " & [FirstName]
ORDER BY [Lastname] & ", " & [FirstName], Right(" " & Right("0" &
DatePart("m",[OrderDate]),2) & "/" & DatePart("yyyy",[OrderDate]),
8-(DatePart("yyyy",[OrderDate])-Year(Date())))
PIVOT Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())));
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
:
I hv this formula as row source
TRANSFORM Count(*) AS [Count] SELECT (Format([DATE_RECEIVED],"MMM 'YY"))
FROM [Query-Chart Startup] GROUP BY (Year([DATE_RECEIVED])*12 +
Month([DATE_RECEIVED])-1),(Format([DATE_RECEIVED],"MMM 'YY")) PIVOT [STATUS];
Any idea on how to select record for year 2006 only...
TQ
