Conditional formatting within Trim function

[Guest]

Hello all,
I have a report in which I'm trying to use the Trim function to pull
together several fields into one legal description. It works great except
when I don't know the Lender.

My statment opens with the following line:
This is to certify to: [ClientName] and [Lender] ....

When I don't have a Lender, I don't want the "and" to be displayed. I am
using Trim because all of my fields are of varying length and there is a lot
of standard text that goes between them.

Any ideas on how to get rid of my "and"?
Thanks!
Melinda

 

[Marshall Barton]

I have a report in which I'm trying to use the Trim function to pull
together several fields into one legal description. It works great except
when I don't know the Lender.

My statment opens with the following line:
This is to certify to: [ClientName] and [Lender] ....

When I don't have a Lender, I don't want the "and" to be displayed. I am
using Trim because all of my fields are of varying length and there is a lot
of standard text that goes between them.

Any ideas on how to get rid of my "and"?

I don't see how Trim can help in this situation. Trim only
removes space characters from the beginning and end of a
string.

You didn't provide any information about where the data is
coming from or how you are trying to "pull together" your
"several fields". But I will guess that you are
concatenating some fields from the form's record source
query using an expression something like:

="This is to certify to: " & ClientName & " and " & Lender &
"...

If that's close, then you can suppress the " and " when the
Lender field is null by using the IIf function:

="This is to certify to: " & ClientName & IIf(Lender Is
Null, ""," and " & Lender) & "...

Or, more concisely:

="This is to certify to: " & ClientName & (" and " + Lender)
& "...

 

[Guest]

Hi Marsh,
Sorry that I wasn't more clear. I was concatenating my data from my query
as you suggested. I have to use trim because some of my users get a little
to zealous with the spacebar.
Your suggestion worked perfectly. I wasn't familiar with the "+" for
concatenation.
Thanks!
Melinda