>= [Month]-3 And <= [Month]. Showing the correct months plus 12,11,10 no matter what.

[PeakH]

This has got to be super simple and I'm going to waste someones time with this but I am missing something.



Query field: Month: Month([OrderDate])
Criteria: >=[Forms]![MainMenu]![Month]-3 And <=[Forms]![MainMenu]![Month]

Used to compare sales over the last 4 months.

When [Forms]![MainMenu]![Month] is equal to any number 2-9, the formula works as it should except that months 10, 11, and 12 are always included.
If the number is 1, it works as it should no problems.
If the number is 10 it only shows 10 ; 11 it shows 11 and 10 ; 12 it shows 12, 11, and 10.

Why is it doing this?

[CJ_London]

if your form month value is 1, then in your criteria, month-3=-2

a) do not use a reserved word for field (or table names) - Month is a reserved word, you are using it in your query field 'Month', so don't be surprised if Access gets confused.

b) you need to use the dateadd function to deduct 3 months

Criteria: >=dateadd("m",-3,[Forms]![MainMenu]![Month]) And <=[Forms]![MainMenu]![Month]

[pbaldy]

I suspect you're getting an alphabetic result rather than a numeric one. That said, the chink in your armor is that it doesn't take year into account (may not be a problem yet, but...). I'd use the actual date and enter the beginning and ending dates you want included. You can do that in the background after letting the user enter a month number, though that would prevent them from looking at previous years.

Using the date in the table also lets you take advantage of indexing. Your current method requires the Month() function be applied to every record in the table.

[PeakH]

I changed all the names so that they aren't "Month" any more. The Query field is MRMonth and the form control is MonthCombo

I did change the format of both the field and the control to be a general number, but it doesn't seem to save that in the query.

I haven't gotten to dealing with the year yet, though I probably ought to so that I don't get the month working and then have to rethink everything when I want a report dealing with November of one year to February of the next.

I have a series of reports that are run off of the main menu using a month combobox and a year combobox, but so far they all only deal with a single month. I would like to keep it consistent if I can for simplicity sake. User friendly-ness is a big part of my job.

I could create a table where each month for the next, say 10 years, has it's own unique number. That way I could use the month number instead of a date, or trying to cross over years?
Unless that is just an extra unnecessary step.

[pbaldy]

My point is that if you ask for month 5, you get May of 2015, 2016, 2017, 2018, etc. I doubt you want that. Like I said, I'd use a specific date range, either entered by the user or derived from what the user enters.

[PeakH]

Right, I do have a year combo box so I can set the year in the query to be equal to the year in the combo box, but that wont allow me to cross over from the end of one year to the beginning of the next.
So I'm trying out what you suggested and having the date range be derived from the combo boxes with some kind of criteria similar to this:

[OrderDate] <=[Forms]![MainMenu]![Monthcombo] & "/31/" & [Forms]![MainMenu]![Yearcombo] And >= ...?

but this only works for months that have 31 days. If I try to do this for February, I get an error but I want to make sure that one end of the range is the last day of the month selected. And I also don't know how to put in the second half of the formula to actually be a range instead of just all dates before what is entered. Since I need the other end of the range to be the first day of whatever month was 3 months before the month entered, I can't rely on the numerical value of the month or year if it crosses over to another year.

Just a bit of added information, I am grouping and summing values by month, to look at all sales in a given month.

[PeakH]

<=[Forms]![Main Menu]![Monthcombo] & "/31/" & [Forms]![Main Menu]![Yearcombo] And >=DateAdd("m",-4,[Forms]![Main Menu]![Monthcombo] & "/31/" & [Forms]![Main Menu]![Yearcombo])


This seems to be working better, but I still don't know what to do about the 31 days.

[aytee111]

Don't use "31" - use "01" of the following month minus 1.

[PeakH]

Had to take a break from the project for a few days, but came back with a new set of eyes which didn't hurt.

So far, this seems to be the winner.

DatePart("m",[orderdate])<=[Forms]![MainMenu]![Monthcombo] And >=DateAdd("m",-3,[Forms]![MainMenu]![Monthcombo] & "/01/" & [Forms]![Main Menu]![Yearcombo])