Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Hollywood, CA
    Posts
    17

    Question Unanswered: If Then condition statement or function?

    I don't know VB...but know MS Access 2000 well.

    Here goes:

    I need the mail classes below translated or converted to the 2 character new mail codes below:


    Mail_Class to New Mail_Class (translated)

    POS-1ST to 1C
    UPS-GNDRES to UG
    UPS-2NDAY to U2
    UPS=NXTDAY to U1


    I need to convert or translate these values in a query. The values stay the same in the table or they can be converted upon import into a table if that's easier, but they need to be translated to the new 2 character code.

    Can I use the IIF function to do this in the query or can I convert them when I import these into a table??

    I'd like to be able to do a "If then elseif" statement, but don't how to do it with a function in an expression.

    Thanks for your help,

    Tony

  2. #2
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Try using the switch function in the contol's expression.
    ="Sales for " & & " in " & Format([InvoiceDate],"mmmm")

    If each one of the items listed below have an Auto Number associated with them then, use the switch function in the contol's expression.

    Switch([Mail_Class]=1,"1C",[Mail_Class]=2,"UG",[Mail_Class]=3,"U2",[Mail_Class]=4,"U1")
    POS-1ST to 1C
    UPS-GNDRES to UG
    UPS-2NDAY to U2
    UPS=NXTDAY to U1

    That should work!
    Gotta to do some code

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    update table
    set column = '1C'
    where column = 'POS-1ST'

    Alternatively,

    update table
    set column =
    CASE
    WHEN column = 'POS-1ST ' THEN '1C'
    WHEN column = 'UPS-GNDRES ' THEN 'UG'
    ...
    ELSE null END;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Oct 2003
    Location
    Hollywood, CA
    Posts
    17
    Thank you Gentleman for your responses...


    I ended up using the IIF Function as follows:

    IIf([tblmail]![mail]="POS-1ST","1C",IIf([tblmail]![mail]="POS-1STPRI","1C",IIf([tblmail]![mail]="POS-BO","MM",IIf([tblmail]![mail]="POS-PRI","1C",IIf([tblmail]![mail]="UPS-GNDRES","UG",IIf([tblmail]![mail]="UPS-GCOM","UG",IIf([tblmail]![mail]="UPS-2NDAY","U2",IIf([tblmail]![mail]="UPS-NEXTAV","U1",IIf([tblmail]![mail]="UPS-NEXTDAY","U1")))))))))

Posting Permissions

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