#53265 - 03/31/16 02:59 PM
Multiple logical tests for IF Expression
|
OL Newbie
Registered: 03/30/16
Posts: 4
|
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
|
|
Top
|
|
|
|
|
#53271 - 04/01/16 10:07 AM
Re: Multiple logical tests for IF Expression
[Re: Jimhhc]
|
JFLAIR
Unregistered
|
Hello Jim, You may want to try this:
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:
IF(LEN(TRIM([Firstname]))=1, "Friend, ",[Firstname])
Hope this helps! Regards, JF
|
|
Top
|
|
|
|
|
#53273 - 04/01/16 04:05 PM
Re: Multiple logical tests for IF Expression
[Re: Jimhhc]
|
OL Newbie
Registered: 03/30/16
Posts: 4
|
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?
|
|
Top
|
|
|
|
|
#53274 - 04/01/16 04:39 PM
Re: Multiple logical tests for IF Expression
[Re: Jimhhc]
|
JFLAIR
Unregistered
|
Hello Jim, You can try this one:
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
Edited by JFLAIR (04/01/16 04:49 PM)
Edit Reason: Added a TRIM()
|
|
Top
|
|
|
|
|
#53275 - 04/01/16 04:56 PM
Re: Multiple logical tests for IF Expression
[Re: Jimhhc]
|
OL Newbie
Registered: 03/30/16
Posts: 4
|
JF! You are AWESOME!!! That last example is perfect. Thank you again.
|
|
Top
|
|
|
|
|
#53419 - 05/10/16 04:01 PM
Re: Multiple logical tests for IF Expression
[Re: Jimhhc]
|
OL Newbie
Registered: 03/30/16
Posts: 4
|
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.
|
|
Top
|
|
|
|
|
|
Previous Topic
Index