Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the following code that I would like to make an Access query calculations from:

    Case
    when[Clps Ackrat Catg C]in('NOR','ASC','ASO','CPL') then ('Experience (Non-Risk')
    when[Clps Ackrat Catg C]in('CFA','CRC') then ('Community')
    when[Clps Ackrat Catg C]=('PRO') then ('Experience (Risk)')
    when[Clps Ackrat Catg C] in ('RFA','RTS') then ('Experience (Semi-Risk)')
    Else Null
    End

    Case
    when[Coprm C]in(14,27) then ('ASO/ASC')
    when[Coprm C]in(32,3) then('Cost Plus')
    else ('Insured')
    end


    I am not sure of the proper syntax

    IIF([Clps ACKrat Catg C] in("Nor","ASC","ASO","CPL"),"Experience(Non-Risk)") & IIF([Clps Ackrat catg c] in ("CFA","CRC"),"Community") & etc


    I am not sure how to do the else Null

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    Switch([Clps Ackrat Catg C] In ("NOR","ASC","ASO","CPL"), "Experience (Non-Risk)", [Clps Ackrat Catg C] In ("CFA","CRC"), "Community", [Clps Ackrat Catg C] = "PRO", "Experience (Risk)", [Clps Ackrat Catg C] In ("RFA","RTS"), "Experience (Semi-Risk)")

    and similar for the other.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780313' date='17-Jun-2009 16:27']Try

    Switch([Clps Ackrat Catg C] In ("NOR","ASC","ASO","CPL"), "Experience (Non-Risk)", [Clps Ackrat Catg C] In ("CFA","CRC"), "Community", [Clps Ackrat Catg C] = "PRO", "Experience (Risk)", [Clps Ackrat Catg C] In ("RFA","RTS"), "Experience (Semi-Risk)")

    and similar for the other.[/quote]

    What does Switch do? If that instead of IIF? What about the null or the else Insured?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The Switch function lets you specify several conditions and return a value depending on which condition is true. The syntax is

    Switch(condition1, returnvalue1, condition2, returnvalue2, ...)

    If none of the conditions is true, Switch returns Null.

    See Switch Function (applies to all versions of Access, at least from Access 97).

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780318' date='17-Jun-2009 16:00']The Switch function lets you specify several conditions and return a value depending on which condition is true. The syntax is

    Switch(condition1, returnvalue1, condition2, returnvalue2, ...)

    If none of the conditions is true, Switch returns Null.

    See Switch Function (applies to all versions of Access, at least from Access 97).[/quote]

    What about if you want "Insured" for all others that are not defined?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use

    Switch([Clps Ackrat Catg C] In ("NOR","ASC","ASO","CPL"), "Experience (Non-Risk)", [Clps Ackrat Catg C] In ("CFA","CRC"), "Community", [Clps Ackrat Catg C] = "PRO", "Experience (Risk)", [Clps Ackrat Catg C] In ("RFA","RTS"), "Experience (Semi-Risk)", True, "Insured")

    The last condition is always True, but "Insured" will only be returned if none of the previous conditions evaluates to True. Alternatively:

    Nz(Switch([Clps Ackrat Catg C] In ("NOR","ASC","ASO","CPL"), "Experience (Non-Risk)", [Clps Ackrat Catg C] In ("CFA","CRC"), "Community", [Clps Ackrat Catg C] = "PRO", "Experience (Risk)", [Clps Ackrat Catg C] In ("RFA","RTS"), "Experience (Semi-Risk)"), "Insured")

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780320' date='17-Jun-2009 17:21']You could use

    Switch([Clps Ackrat Catg C] In ("NOR","ASC","ASO","CPL"), "Experience (Non-Risk)", [Clps Ackrat Catg C] In ("CFA","CRC"), "Community", [Clps Ackrat Catg C] = "PRO", "Experience (Risk)", [Clps Ackrat Catg C] In ("RFA","RTS"), "Experience (Semi-Risk)", True, "Insured")

    The last condition is always True, but "Insured" will only be returned if none of the previous conditions evaluates to True. Alternatively:

    Nz(Switch([Clps Ackrat Catg C] In ("NOR","ASC","ASO","CPL"), "Experience (Non-Risk)", [Clps Ackrat Catg C] In ("CFA","CRC"), "Community", [Clps Ackrat Catg C] = "PRO", "Experience (Risk)", [Clps Ackrat Catg C] In ("RFA","RTS"), "Experience (Semi-Risk)"), "Insured")[/quote]


    Thanks for the explanation....

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='LindaR' post='780298' date='18-Jun-2009 01:06']I have the following code that I would like to make an Access query calculations from:

    Case
    when[Clps Ackrat Catg C]in('NOR','ASC','ASO','CPL') then ('Experience (Non-Risk')
    when[Clps Ackrat Catg C]in('CFA','CRC') then ('Community')
    when[Clps Ackrat Catg C]=('PRO') then ('Experience (Risk)')
    when[Clps Ackrat Catg C] in ('RFA','RTS') then ('Experience (Semi-Risk)')
    Else Null
    End

    Case
    when[Coprm C]in(14,27) then ('ASO/ASC')
    when[Coprm C]in(32,3) then('Cost Plus')
    else ('Insured')
    end


    I am not sure of the proper syntax

    IIF([Clps ACKrat Catg C] in("Nor","ASC","ASO","CPL"),"Experience(Non-Risk)") & IIF([Clps Ackrat catg c] in ("CFA","CRC"),"Community") & etc


    I am not sure how to do the else Null[/quote]
    You could use a lookup table with an outer join for the Null condition. The table would have [Clps ACKrat Catg C] and an extra field for the description that needs to be returned.

Posting Permissions

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