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" 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 FuntionNext: Viewing expressions in a listbox