Criteria: Display data between two dates and YTD

[accessaspire219]

I have a database that calculates monthly on hand inventory and YTD on hand inventory. Each month is displayed as 01/2009, 02/2009, 03/2009 ....etc and there is a YTD number e.g. 2009

The above query is a crosstab query in which the employee name is a row heading, period (mm/yyyy and yyyy) are column heading while the actual MIOH is the value.

This query has a criteria in the period which allows the user to input from and two dates to determine the data he wants to view. Currently I use the criteria Between [Start Date] And [End Date] however,with this criteria the year is always left out. Meaning if the criteria is 01/2009 to 05/2009 then the 2009 (YTD) numbers are not displayed. Is there a way in which I can specify the criteria so that it will mean "Between start date and end date AND this year. So that it will also display the year column along with 01/2009, 02/2009, 03/2009, 04/2009 and 05/2009.

Any help is appreciated!
Thanks.

 

[boblarson]

Use a FORM for input of parameters. This way you can simply get the portion of either the begin or end date for use as the year.

 

[accessaspire219]

I am using a form to enter the dates. I did not fully understand your suggestion, can you please explain it to me?

I thought there would be a way ot modify the criteria that I have specified in the query Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate] to include the year so that any record with just 2009 instead of 01/2009 is also picked up.

 

[boblarson]

For the Year criteria for your year to date field, you could use something like

Format([Forms].[Form1]![StartDate],"yyyy")

or

Year([Forms].[Form1]![StartDate])

 

[accessaspire219]

I get your point, but the issue is that the YTD and Monthly values are in the same column. So it will have to be one criteria for that column.

 

[boblarson]

Maybe posting the SQL for the query will help. If I can't help with it (SQL is not my strongest suit), perhaps someone else can pick it up.

 

[accessaspire219]

Sure. Here it is

PARAMETERS [Forms].[Form1]![StartDate] Text ( 255 ), [Forms].[Form1]![EndDate] Text ( 255 ), [Forms].[Form1]![Year] Text ( 255 );
TRANSFORM Sum(Union.METRIC) AS SumOfMETRIC
SELECT Union.ABUYR, Union.TYPE
FROM [Union]
WHERE (((Union.PERIOD1) Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]))
GROUP BY Union.ABUYR, Union.TYPE
PIVOT Union.PERIOD1;