Multiple logical tests for IF Expression

Posted by: Jimhhc

Multiple logical tests for IF Expression - 03/31/16 02:59 PM

Hello,
I can't figure out how to have multiple logical tests in an IF Expression. I have a typical database with names and addresses. In the [Firstname] data field there are some full names and some initials. If there is a name, we want the merged letter to say, Dear Whateverthenameis, if there is an initial, we want the merged letter to say, Dear Friend,.

I have written the expression:
IF([Firstname]="A","Friend,", [Firstname]) and it works fine but I can't figure out how to include the initials, B, C, D,...Z.

I have tried adding another expression at the end, replacing the initial A with B, etc. I get errors like, "End of expression expected" or "Logical Expected". I can find nothing to explain the proper syntax or any clue to what I am leaving out.

Thanks if you can help.
Jim
Posted by: Anonymous

Re: Multiple logical tests for IF Expression - 04/01/16 10:07 AM

Hello Jim,

You may want to try this:
Code:
IF(LEN(TRIM([Firstname]))=1,IF(CONTAINS("A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z", [Firstname]), "Friend, ", "Does not match any letters"),[Firstname])

The first if checks the length of the field Firstname. If it only have 1 character then we go in the true statement and we check if it is finding one of the letters (that part could be technically skipped as we checked prior how many characters it contained and just put the actual "Friend,"). Now for the false answer to that second if is completely up to you if it doesn't match any of the letters. Then the false section to the first if, which means it has more than 1 characters.

Or this simplify code:
Code:
IF(LEN(TRIM([Firstname]))=1, "Friend, ",[Firstname])


Hope this helps!
Regards,
JF
Posted by: Jimhhc

Re: Multiple logical tests for IF Expression - 04/01/16 04:05 PM

Thank you JFLAIR! That gets me 99% there. I hadn't considered a Firstname field with two initials separated by a space, as in "W C" which returns "Dear W C". I'd like to include these 2-initial records with the salutation, "Dear Friend". I tried editing the first example expression you gave with ;W ; but that didn't work. Can you suggest a tweak to accommodate this?
Posted by: Anonymous

Re: Multiple logical tests for IF Expression - 04/01/16 04:39 PM

Hello Jim,

You can try this one:
Code:
IF((LEN(TRIM([Firstname]))=1) OR (LEN(TRIM([Firstname]))=3 AND POS(TRIM([Firstname])," ",2)=2) , "Friend, ",[Firstname])


Instead of trying to check all of possible 2 letters combination you can have, this function checks if it has a space in between the 3 characters found or if it has only 1 character.

Hope this helps!
Regards,
JF
Posted by: Jimhhc

Re: Multiple logical tests for IF Expression - 04/01/16 04:56 PM

JF! You are AWESOME!!! That last example is perfect. Thank you again.
Posted by: Jimhhc

Re: Multiple logical tests for IF Expression - 05/10/16 04:01 PM

Hi again JF! I've discovered some other first name variables in our database that are causing the expression you crafted to not work. For example, if there is a name abbreviation without a period such as "Wm", this is getting through the filter. I would like these also to get the "Dear Friend" situation. Any ideas?
Thanks.