Results 1 to 9 of 9

Thread: OR Expressions

  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: OR Expressions

    I am trying to create an expression in a query that will select data from a particular field that can then be used to calculate on another field.

    What is happening here is that depending on the month a different period is required.

    This is my expression but it does not work – it will work with one “OR” statement but with more than one “OR” statement it throws up an error.

    IIf(
    [MONTH]=7,[Period 1]
    Or [MONTH]=8,[Period 2]
    Or [MONTH]=9,[Period 3]
    Or [MONTH]=10,[Period 4]
    Or [MONTH]=11,[Period 5]
    Or [MONTH]=12,[Period 6]
    Or [MONTH]=1,[Period 7]
    Or [MONTH]=2,[Period 8]
    Or [MONTH]=3,[Period 9]
    Or [MONTH]=4,[Period 10]
    Or [MONTH]=5,[Period 11]
    Or [MONTH]=6,[Period 12] )

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    IIf([MONTH]=7,[Period 1],
    IIf([MONTH]=8,[Period 2],
    IIf([MONTH]=9,[Period 3],
    IIf([MONTH]=10,[Period 4],
    IIf([MONTH]=11,[Period 5],
    IIf([MONTH]=12,[Period 6],
    IIf([MONTH]=1,[Period 7],
    IIf([MONTH]=2,[Period 8],
    IIf([MONTH]=3,[Period 9],
    IIf([MONTH]=4,[Period 10],
    IIf([MONTH]=5,[Period 11],[Period 12]))))))))))))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Perfect,
    thanks

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    an observation that might be useful:
    ([MONTH] + 6) mod 12
    provides period # directly without the nested iif()

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, izy, what if [Period n] are buckets holding amounts like sales total for the period
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Rudy!

    i did say 'might be useful'.
    mod finds #
    ...but if it can't get squeezed into the SQL, it's useless.

    this smells like a "translator" from calendar-month to fiscal-month. i do that with those famously despised calculated values, keeping both fis and cal months in the table. saves deciding between iif() and mod.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    at first i too thought it was a calendar-fiscal conversion

    but why then would the expression select variables e.g. [Period 1] rather than the fiscal integer that corresponds to the month?

    unless there are twelve [Period n] variables which contain, um, values of n

    i need to go lay down for a while...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I'll agree on the lie down - this seems like an unnormalized structure, in addition to using reserved words as name of the field.

    Choose([MONTH],[Period 1], [Period 2], [Period 3], [Period 4], [Period 5], [Period 6], [Period 7], [Period 8], [Period 9], [Period 10], [Period 11], [Period 12] )
    Roy-Vidar

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    choose?! never seen that before, but it shore is sweet
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •