Need help with Expression in Query

[rookie77]

I have an existing query for 12 month order history. In the criteria it asks for the number of months therefore when the report is ran, you are prompted to enter the number of months you are inquiring about. Now I need to create a calculation based upon that entry. So if I was to run the report for a 3 month period I want the calculations based upon sales for month 1 through 3. But if I was to run the report for a 5 month period I want the calculations based upon sales for month 1 through 5. And so on. I'm just learning Access and don't have these figured out quite yet. Any assistance would be appreciated.

[orange]

Look at the DateAdd function
see http://www.techonthenet.com/access/f...te/dateadd.php There are other date functions there also Month, Year

You might use something like this (untested)

Let's suppose the user types into a field called intField, and suppose he types in 7.

Your query for the report would be along these lines

Select yourdata from yourtable
where Month(SalesDate) Between 1 and intField

[rookie77]

I don't think that approach will work in my case simply because its not an actual 'date' per say with a year and month and all that. The way it works is Demand 1 is always always the 'current' month. The previous month is always demand 2 and the month prior to that is always demand 3 and so on. So, I could be wrong but I don't think the dateadd function would work here. I'm working with a report and query that was created by someone else and am attempting to make one change to get the correct results. In the criteria it shows >=[> = 12 Month Demand?] which in turn prompts you when running the report to select how many months you want to run. So I'm trying to find a way to use whatever is entered there to determine what the report shows. In my mind I'm thinking it should be like an if statement probably something like if 3 then demand 1 + demand 2 + demand 3. I'm just not educated enough in access quite yet to determine the proper way to structure this for it to work correctly.

[orange]

The DateAdd was just a thought for adding a month or x months to a date (todays date).

Did you see my select query's where clause. I think that is more along the lines of what you want.
You will need some logic to relate your demand1, demand2 etc to an actual month range (I think).

I also think you might have a form. The form would be used to capture the demand info, verify the entry, then translate that and build a query with the demand info. Use that query as the recordsource for the Report and then Open that report from a Button on the Form.

Good luck.