Calculate Quantity between dates

[Carrie_Loos via AccessMonster.com]

Hi, I have been trying to sum quantities between dates without success. In my
last attempt I get an error message of "you tried to execute a query that
does not include the specified expression ([SumOfOrd Qty], [SumofOrd Qty]
Between DateAdd("m",-12,[MROs]![Date Open]) And DateAdd("m",13,[MROs]![Date
Open]) as part of an aggregate function." Can anyone explain what I do not
understand?


PARAMETERS [Month] DateTime;
SELECT MROs.[Core Part Nbr], Format([Date Open],"mmm-yy") AS [Month], Sum
(MROs.[Ord Qty]) AS [SumOfOrd Qty], [SumofOrd Qty] Between DateAdd("m",-12,
[MROs]![Date Open]) And DateAdd("m",13,[MROs]![Date Open]) AS Forecast1
FROM MROs
GROUP BY MROs.[Core Part Nbr], Format([Date Open],"mmm-yy"), [SumofOrd Qty]
Between DateAdd("m",-12,[MROs]![Date Open]) And DateAdd("m",13,[MROs]![Date
Open]);

 

[John Spencer]

The following will give you sums for the each month for a period between
12 months ago and 13 months into the future for each CorePart Nbr.


SELECT MROs.[Core Part Nbr]
, Format([Date Open],"mmm-yy") AS [Month]
, Sum(MROs.[Ord Qty]) AS [SumOfOrd Qty]AS Forecast1

FROM MROs

WHERE [Date Open] Between DateAdd("m",-12,Date()) And DateAdd("m",13,Date())

GROUP BY MROs.[Core Part Nbr]
, Format([Date Open],"mmm-yy")


Your posted query doesn't give sufficient information about what you
actually are attempting to achieve and neither does your description.

You had quite a few syntax errors in what you posted.
You declared a Parameter Month as a DateTime and then aliased a
calculation Format([Date Open],"mmm-yy") which generates a STRING as Month.

You stuck a between ... and .. comparison operation in the SELECT
clause. Normally you see this in a WHERE clause or a Having clause.




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Carrie_Loos via AccessMonster.com]

This is great, thank you. I apologize for the vagueness, it is difficult to
express needs when you don't really understand how to present it. One more
question, if you will, How do you add yet another period for a different set
of dates, my attempts were unsuccessful. {i.e. Forecast2 WHERE [Date Open]
Between DateAdd("m",-11,Date()) And DateAdd("m",1,Date())}?


SELECT MROs.[Core Part Nbr]
, Format([Date Open],"mmm-yy") AS [Month]
, Sum(MROs.[Ord Qty]) AS [Forecast1]

FROM MROs

WHERE [Date Open] Between DateAdd("m",-12,Date()) And DateAdd("m",0,Date())

GROUP BY MROs.[Core Part Nbr]
, Format([Date Open],"mmm-yy")

The following will give you sums for the each month for a period between
12 months ago and 13 months into the future for each CorePart Nbr.

SELECT MROs.[Core Part Nbr]
, Format([Date Open],"mmm-yy") AS [Month]
, Sum(MROs.[Ord Qty]) AS [SumOfOrd Qty]AS Forecast1

FROM MROs

WHERE [Date Open] Between DateAdd("m",-12,Date()) And DateAdd("m",13,Date())

GROUP BY MROs.[Core Part Nbr]
, Format([Date Open],"mmm-yy")

Your posted query doesn't give sufficient information about what you
actually are attempting to achieve and neither does your description.

You had quite a few syntax errors in what you posted.
You declared a Parameter Month as a DateTime and then aliased a
calculation Format([Date Open],"mmm-yy") which generates a STRING as Month.

You stuck a between ... and .. comparison operation in the SELECT
clause. Normally you see this in a WHERE clause or a Having clause.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi, I have been trying to sum quantities between dates without success. In my
last attempt I get an error message of "you tried to execute a query that

[quoted text clipped - 11 lines]

Between DateAdd("m",-12,[MROs]![Date Open]) And DateAdd("m",13,[MROs]![Date
Open]);