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 wholenumber 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 20022005, 20072010 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 wholenumber 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 20022005, 20072010 > 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 wholenumber 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 