If statemtns

Topic

New Reply

How can I code the “if” statements on the attached so that I cna get the results shown in the sample table?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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)

Reply | Quote