Need query to pickup last two months

[Angel69]

I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year?? TIA

CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)

 

[Vagus14]

Can you provide a sample of the data and what you want it to look like. There's not enough here to answer your question.

 

[plog]

First, 'Month' is a poor choice for a field name because its a reserved word and will cause you issues when writing code. I suggest you rename it and any other fields that are reserved words (http://support.microsoft.com/kb/286335). Prefix themt with what that Month represents (i.e. HireMonth, SalesMonth, etc.).

I agree with Vagus in that we do not know enough about your table to help you specifically. But I can tell you generically how you need to accomplish this.

The issue you have is because when you run a report for January it doesn't look back 2 months to November, it looks back 2 integers to -1. If you want to look back 2 months, you need to work with dates, not integers.

This is going to take 3 things:

1. Reconfigure your form so the user inputs a month and a year, or a date.

2. Reconfiguring your query using Access Date functions (http://www.techonthenet.com/access/functions/) to determine the date 2 months prior to the input.

3. A field more detailed than your current 'Month' field. You will need a field that has the Year, or a field that is a date.