John,
I've seen your response to this query in numerous forums. Thanks for the easy fix....EXCEPT I'm a VB novice. Where exactly do I find the expression to replace with your expression: ((Month([Order Date])-1) MOD 3) + 1
Thank you!
Tim
John Spencer wrote:
If you are going to use MOD to number the months from 1 to 3, then try
16-Dec-09
If you are going to use MOD to number the months from 1 to 3, then try the
following expression.
((Month([Order Date])-1) MOD 3) + 1
MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to
the result of the Mod Operation.
You also want 1,4,7 and 10 to return zero when using MOD so you need to
subtract 1 before using MOD so you get
0 for months 1,4,7,10
1 for months 2,5,8,11
2 for months 3,6,9,12
Then you add 1 to the result to get 1,2, or 3.
This may or may not fix the fact that sales for December do not show up. I
suggest you post the SQL of the query you are using.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Coco111 via AccessMonster.com wrote:
Previous Posts In This Thread:
Help in Sales Analysis - Northwind template
Anybody Please help... Sorry again, in Northwind template 2007. I found
something wrong in sales Analysis, but I dont know how to solve.
Queries of sales analysis show below-
MonthOfQuarter: (Month([Order Date]) Mod 3), in data sheet view oct = 1, Nov
= 2, Dec = 0 and sales of Dec did not show up. I guess Dec should be 3, if
im not wrong. What do you think and how to solve the problem. The sales of
dec month did not show up...it shows no sales or 0 in quarter sales report
summary. Thank you.
--
If you are going to use MOD to number the months from 1 to 3, then try
If you are going to use MOD to number the months from 1 to 3, then try the
following expression.
((Month([Order Date])-1) MOD 3) + 1
MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to
the result of the Mod Operation.
You also want 1,4,7 and 10 to return zero when using MOD so you need to
subtract 1 before using MOD so you get
0 for months 1,4,7,10
1 for months 2,5,8,11
2 for months 3,6,9,12
Then you add 1 to the result to get 1,2, or 3.
This may or may not fix the fact that sales for December do not show up. I
suggest you post the SQL of the query you are using.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Coco111 via AccessMonster.com wrote:
Hi John,I change expression ((Month([Order Date])-1) MOD 3) + 1 as suggested...
Hi John,
I change expression ((Month([Order Date])-1) MOD 3) + 1 as suggested....
It works....Absolute right, you are great...fast response and help me solve
problem...i can see the sales of dec in report now..
But...now, im in Q4, if in Jan'10 Q1 what I need to do next or do nothing...
But im not very clear about your below advice
You also want 1,4,7 and 10 to return zero when using MOD so you need to
subtract 1 before using MOD so you get
0 for months 1,4,7,10
1 for months 2,5,8,11
2 for months 3,6,9,12
Then you add 1 to the result to get 1,2, or 3.
Sorry and thanks....Coco111
John Spencer wrote:
--
Do nothing.
Do nothing. The "advice" was simply an explanation of how the expression
worked to give you month number of the quarter
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Coco111 via AccessMonster.com wrote:
Noted, and many thanks....
Noted, and many thanks....
John Spencer wrote:
--
Submitted via EggHeadCafe - Software Developer Portal of Choice
C# And The Little Iterator That Could
http://www.eggheadcafe.com/tutorial...32-0ae26adaa533/c-and-the-little-iterato.aspx
