Results 1 to 5 of 5

Thread: If statemtns

  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How can I code the "if" statements on the attached so that I cna get the results shown in the sample table?
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bbrown5 View Post
    How can I code the "if" statements on the attached so that I cna get the results shown in the sample table?
    I think you're over-complicating it. Try using the NZ function:-

    Role = nz(Role1) & nz(Role2) & nz(Role3) & nz(Role 4) &nz(Role 5)

    This will build a string, contatenating the value if there is anything a Role(n) field, or nothing if it's null.

    You can substitute another value instead of null, if you wish, by specifying a value after the field name:-

    nz(Role1,"-")

    NZ also works with numeric fields, but defaults to 0 rather than null as the returned value.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Just want to clarify - you want any roles that have data in them returned, with an & between them?

    You don't put put in any spaces? Would you prefer some spaces?

    e.g. You have : Biller1&Biller2&Biller3&Biller4&Biller5

    Would Biller1 & Biller2 & Biller3 & Biller4 & Biller5 be better? It is more readable.

    I think I would use the + operator. + returns nothing if either bit is Null

    ([role1]+ " & ") & ([role2]+ " & ") & ([role3]+ " & ") & ([role4]+ " & ") & ([role5]+ " & ")

    But this would have an extra " & " on the end, but we don't know in advance which one is not needed. So we just remove the last three characters using the left function.

    left(([role1]+ " & ") & ([role2]+ " & ") & ([role3]+ " & ") & ([role4]+ " & ") & ([role5]+ " & "), len(([role1]+ " & ") & ([role2]+ " & ") & ([role3]+ " & ") & ([role4]+ " & ") & ([role5]+ " & "))-3)

    But if all the roles are empty this would throw an error, so should test that len >3
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    With the nz Function I am ending up with:

    Role1&&&&
    Role1&Role2&&&

    Because this information will become a part of a User File Import I do need the exact format when there are multiple roles w/o spaces: Role1&Role2

    WIth John's response I get the closest to what I need however don't need the & at the end when there are multiple roles:

    Role1
    Role1&Role2&

    How do I element the final & when there are multiple roles?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    How do I element the final & when there are multiple roles?
    The second part of my solution told you how to get rid of the final &.

    Code:
    left(([role1]+ " & ") & ([role2]+ " & ") & ([role3]+ " & ") & ([role4]+ " & ") & ([role5]+ " & "), len(([role1]+ " & ") & ([role2]+ " & ") & ([role3]+ " & ") & ([role4]+ " & ") & ([role5]+ " & "))-3)
    But if you remove the spaces I put in, then change the 3 on the end to 1.

    Added later

    It is hard to see what is going on in the above because it is so long.

    To trim n characters off the end of a piece of text (string), use the left function as follows.

    Left(string, Len(string)-n)

    If there is a risk that string will be less than n characters long, test for that:

    iif(len(string)>n,Left(string, Len(string)-n),string)
    Regards
    John



Posting Permissions

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