locked
IIf or switch function access 2007 RRS feed

  • Question

  • hi. i am getting alot of problems trying to creat a iff function in a querry i have in access 2007. i am currently trying to change the months which i have in a field column. i would like to display it as which quarter it is in.

    i thought that if i put the following function i would get it

    IIf([Month]<=1 And >=3;"Q1";IIf ([Month]<=4 And >=6;"Q2"............ect

    but i keep getting an error can someone please tell me what is my best option and how would i write it

    Thanks

    Monday, July 23, 2012 7:36 PM

Answers

  • IIf([Month]=>1 And =<3,...

    won't work either, you have to repeat the field name:

    IIf([Month]>=1 And Month<=3,...

    But even easier:

    =Switch([Month]<=3, "Q1", [Month]<=6, "Q2", [Month]<=9, "Q3", [Month]<=12, "Q4")

    Evaluation will stop at the first condition that evaluates to True, so for example if [Month]=5, the first condition is not satisfied, but the second one is, so "Q2" will be returned. It doesn't matter that the third and fourth conditions are also satisfied - evaluation doesn't get that far.


    Regards, Hans Vogelaar

    • Marked as answer by Vanderghast Monday, July 23, 2012 8:17 PM
    Monday, July 23, 2012 8:04 PM

All replies

  • You can use

    Quarter: "Q" & ([Month]-1)\3+1


    Regards, Hans Vogelaar

    • Proposed as answer by KCDW Monday, July 23, 2012 8:16 PM
    Monday, July 23, 2012 7:46 PM
  • thanks looking over 2 hrs now how to. once again thanks
    Monday, July 23, 2012 7:48 PM
  • IIf([Month]<=1 And >=3;"Q1";IIf ([Month]<=4 And >=6;"Q2"...........

    I like Hans' response.

    Just so you'll know whats up with your code look at the >< and I have always seen this , vs. ;

    IIf([Month]=>1 And =<3,"Q1",IIf([Month]=>4 And =<6,"Q2",


    Chris Ward

    Monday, July 23, 2012 7:52 PM
  • IIf([Month]=>1 And =<3,...

    won't work either, you have to repeat the field name:

    IIf([Month]>=1 And Month<=3,...

    But even easier:

    =Switch([Month]<=3, "Q1", [Month]<=6, "Q2", [Month]<=9, "Q3", [Month]<=12, "Q4")

    Evaluation will stop at the first condition that evaluates to True, so for example if [Month]=5, the first condition is not satisfied, but the second one is, so "Q2" will be returned. It doesn't matter that the third and fourth conditions are also satisfied - evaluation doesn't get that far.


    Regards, Hans Vogelaar

    • Marked as answer by Vanderghast Monday, July 23, 2012 8:17 PM
    Monday, July 23, 2012 8:04 PM
  • ashiv wrote:
    > thanks tried that also and it worked but i like Hans' response its
    > easier. i know this would be another topic but does anyone know how
    > to change the numbers i have in the month feild to the name? ie 1 =
    > Jan, 2 = Feb . thanks ps im new to access and trying to make a
    > database for school. thanks again
     
    =MonthName([Month])
     
    BTW You shouldn't name a user defined field "Month" because this is the name
    of the VBA function that extracts the month from a date and could lead to
    problems.
     
    --
    cu
    Karl
    ******************
    Access-FAQ (German/Italian): http://www.donkarl.com
     
     
     
    Monday, July 23, 2012 8:07 PM
  • how to change the numbers i have in the month feild to the name? ie 1 = Jan, 2 = Feb .

    You could add a calculated field

    M: MonthName([Month], True)

    The True argument specifies that the month name will be abbreviated to Jan, Feb etc.

    If you use False or omit the second argument, you get the full names January, February etc.


    Regards, Hans Vogelaar

    Monday, July 23, 2012 8:08 PM
  • thanks i delete it cuz i figure it out after but thanks for the reply

    Monday, July 23, 2012 8:12 PM