Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If statement (Access 2000)

    I want to be able to translate the values in UDF9 (07, 08, 05, 03) into a different text values. I know I'll need a nested If statement, but I can't get the first one to work.

    Some background - the codes (07, etc) represent divisions. I'm trying to translate the values into values we use in other searches (DEMD, etc.) so they can combine the data. Let me know if you can figure out how to do this. Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: If statement (Access 2000)

    Are you wanting to do this in a query or in VBA? The answer is somewhat different for each:
    In a query, if you only have four you might use a nested IIF statement, but if you have several more, you might want to simply create a table that has the value mapping, and then create a join between the table the contains UDF9 and the new table that does the mapping.
    If you are doing this in VBA, then you could use a Select Case syntax or a nested IF structure.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Access 2000)

    Actually it will be just 4, i wrote a nested IIF and it came out wrong

    my data goes is this
    03 = DECD
    05 = DEPD
    07 = DEMD
    08 = DESD

    How could i write a nested IFF to be used under the criteria in the query??

  4. #4
    Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Access 2000)

    Put this in the Field Line of the query and change

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: If statement (Access 2000)

    As noted, I'd also use a table to translate the codes. Otherwise, recommend use Switch function to translate codes in place of nested IIf statements. Example of query SQL:

    SELECT Table5.UDF9, Switch([UDF9]="03","DECD",[UDF9]="05","DEPD",[UDF9]="07","DEMD",[UDF9]="08","DESD") AS DIV
    FROM Table5
    ORDER BY Table5.UDF9;

    See attd pic of query results using this SQL. If the first expression in the pair is true, the function will return the value in second part. The expressions are evaluated from left to right. If none are true, the function returns Null. Check the Help file for more info on Switch.

    HTH
    Attached Images Attached Images

Posting Permissions

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