Need a formula to Calculate Date Ranges

[nagiese]

Currently I have a series of parameter queries that require the user to input the beginning and ending date range to calculate the 4,6,9,12 & Left-to-Go month forecasts, it is cumbersome and it leaves lots of room for human error to leave these queries as human input required. Does anyone know a formula that I could use instead? It would have to refer to the Fiscal year and period that is input into the beginning form as the starting point then it would have to eventually calculate the ranges in yyyypp format. I would need a formula that would calculate the range for 4 month period, 6 month period, 9 month period, 12 month period, & Left- to - Go period (= current period+1 till end of year). An example of my current query is below.

INSERT INTO [mtb-9mnth Dmd Fcst] ( [Import Year], [Import Period], [New League], [New Group], Style, [Color Code], [SumOfResultant Forecast], [AvgOfStandard Cost] )
SELECT [Demand fcst].[Import Year], [Demand fcst].[Import Period], [Demand fcst].[New League], [Demand fcst].[New Group], [Demand fcst].Style, [Demand fcst].[Color Code], Sum([Demand fcst].[Resultant Forecast]) AS [SumOfResultant Forecast], Avg([Demand fcst].[Standard Cost]) AS [AvgOfStandard Cost]
FROM [Demand fcst]
WHERE ((([Demand fcst].[Forecast Year Period]) Between [Enter Beginning YearPd for 9 mnth:] And [Enter Ending YearPd for 9 mnth:]))
GROUP BY [Demand fcst].[Import Year], [Demand fcst].[Import Period], [Demand fcst].[New League], [Demand fcst].[New Group], [Demand fcst].Style, [Demand fcst].[Color Code]
HAVING ((([Demand fcst].[Import Year])=[Forms]![frm-Year_Pd]![Year]) AND (([Demand fcst].[Import Period])=[Forms]![frm-Year_Pd]![Period]) AND (([Demand fcst].[New League]) Not In ("BLANKS","HARLEY")));

If these queries would be better written as VBA I'm not apposed to doing that, I just didn't know how to go about doing that.

Any help would be greatly appreciated
Thanks in advance Nena

[June7]

Yes, can do that. Can't completely eliminate user input but can structure query and form to minimize invalid criteria input.

But why are you saving aggregate data to table?

[nagiese]

Yes, can do that. Can't completely eliminate user input but can structure query and form to minimize invalid criteria input.

But why are you saving aggregate data to table?

I have to save the data to a table becuase it it exported out to an Excel file and then pivot reports are created from the data. How do I calculate the month ranges based on the input for the current period and year that is currently inputted into the form by the user?

[June7]

Not sure you 'have' to save to table. Queries can be exported to Excel.

Not quite sure what you are asking for. If you want the parameters to reference controls on form where user inputs criteria, something like:

WHERE ((([Demand fcst].[Forecast Year Period]) Between [Forms]![formname]![beginningcontrolname] And [Forms]![formname]![endingcontrolname])))

[nagiese]

These are a series of append queries, that utilize the same two tables to extract and calculate what excess inventory would be at certain period ranges, i.e. one append query appends the 4 month range, one appends the 6 month range, etc... I have to use separate append queries and append the data to one table because I'm using the data to compare against the various ranges (ex. Item xyz had inventory excess of 100 units at 4 months, by 6 months it has 90 units, at 12 months it still has 90 units; this tells me that there are no foretasted sales for Item xyz past 4 months, which indicates an inventory issue.) Currently each of my append queries ask the user to input the period range (ex. if your current period is 4 your next 120 day(4 month) period range would be 5 and 8). I'm looking for a function(formula) that would configure the period range based on what the current period input is on a form. Dose a formula like that exist if so How would I write that in an access query criteria field?

[June7]

A start date can be input then use DateAdd function to calculate end date. The Interval, Number, Date arguments can all be provided by reference to inputs on a form.