Surprisingly fidly query?

[Leslie Isaacs]

Hello All

I'm sure this should be simple - but can't seem to get it!

I need a query that takes various integer values (say 'monthnumber'), and
returns an integer (say 'period') between 1 and 12, according to the
following pattern:

'monthnumber' 1 returns 'period' 1
'monthnumber' 2 returns 'period' 2
'monthnumber' 3 returns 'period' 3
etc etc until
'monthnumber' 13 returns 'period' 1
'monthnumber' 14 returns 'period' 2
etc etc until
'monthnumber' 25 returns 'period' 1
'monthnumber' 26 returns 'period' 2
etc etc
i.e. 'period' must generally be the whole-number remainder after dividing
'monthnumber' by 12 - except if the remainder is zero, 'period' must be 12!

I've tried all the obvious ways, but can't get a single formula to do this.
I guess I could do it with some 'If' clauses, but that doesn't seem right.

Hope someone can help.
Many thanks
Les

 

[John Spencer]

(([MonthNumber] -1) Mod 12) + 1

13 becomes 12; Mod 12 is 0 ; add 1 is 1
24 becomes 23; Mod 12 is 11; add 1 is 12

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Leslie Isaacs]

John

That's it!
Why didn't I know about the MOD function: well at least - now I do!

Thanks again
Les

(([MonthNumber] -1) Mod 12) + 1

13 becomes 12; Mod 12 is 0 ; add 1 is 1
24 becomes 23; Mod 12 is 11; add 1 is 12

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hello All

I'm sure this should be simple - but can't seem to get it!

I need a query that takes various integer values (say 'monthnumber'), and
returns an integer (say 'period') between 1 and 12, according to the
following pattern:

'monthnumber' 1 returns 'period' 1
'monthnumber' 2 returns 'period' 2
'monthnumber' 3 returns 'period' 3
etc etc until
'monthnumber' 13 returns 'period' 1
'monthnumber' 14 returns 'period' 2
etc etc until
'monthnumber' 25 returns 'period' 1
'monthnumber' 26 returns 'period' 2
etc etc
i.e. 'period' must generally be the whole-number remainder after dividing
'monthnumber' by 12 - except if the remainder is zero, 'period' must be
12!

I've tried all the obvious ways, but can't get a single formula to do
this. I guess I could do it with some 'If' clauses, but that doesn't seem
right.

Hope someone can help.
Many thanks
Les