Prev: Min Funtion
Next: Viewing expressions in a listbox
From: Leslie Isaacs on 24 Apr 2010 13:42
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


From: John Spencer on 24 Apr 2010 14:27
(([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 wrote:
> 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
>
>
From: Leslie Isaacs on 24 Apr 2010 17:49
John

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

Thanks again
Les


"John Spencer" <spencer(a)chpdm.edu> wrote in message
news:%23dldRv94KHA.1932(a)TK2MSFTNGP05.phx.gbl...
> (([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 wrote:
>> 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


 | 
Pages: 1
Prev: Min Funtion
Next: Viewing expressions in a listbox