How to suppress blanks in a "=Trim..." Text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running a report in which I have several =Trim text boxes combining
different addresses (i.e =Trim([Address1]&", "&[AddressCity]&",
"&[AddressState]&" "&[AddressZip]). This is running off of a contact
management database (imported into Access). So there are several Address
fields: Business address, home address, other address, etc.) I want to print
those that are filled in, but not print lines when the other address fields
are empty. Once I do the =Trim and add the ", " in there, it prints lines
with the commas , because I'm "forcing" them. How can I get it to eliminate
that line in the report if the fields listed in that text box are null?

Thanks. Kathy.
 
I've never used this before. From looking through Help, it looks like this
is what I should put in the Properties of that Text Box. Is this correct?
Is it False action just "Null"?


=IIf([HomeStreet] Is Not Null,Trim([HomeStreet] & ", " & [HomeCity] & ", " &
[HomeState] & " " & [HomePostalCode]),Null)


Jeff Boyce said:
Kathy

Add in the IIF() function. If a field is null, don't add anything. If it
isn't, add the field and your ", ".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Kathy said:
I am running a report in which I have several =Trim text boxes combining
different addresses (i.e =Trim([Address1]&", "&[AddressCity]&",
"&[AddressState]&" "&[AddressZip]). This is running off of a contact
management database (imported into Access). So there are several Address
fields: Business address, home address, other address, etc.) I want to print
those that are filled in, but not print lines when the other address fields
are empty. Once I do the =Trim and add the ", " in there, it prints lines
with the commas , because I'm "forcing" them. How can I get it to eliminate
that line in the report if the fields listed in that text box are null?

Thanks. Kathy.
Click to expand...
Click to expand...
 
Never mind. I fiddled with it and got it to work. Thanks!

Jeff Boyce said:
Kathy

Add in the IIF() function. If a field is null, don't add anything. If it
isn't, add the field and your ", ".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Kathy said:
I am running a report in which I have several =Trim text boxes combining
different addresses (i.e =Trim([Address1]&", "&[AddressCity]&",
"&[AddressState]&" "&[AddressZip]). This is running off of a contact
management database (imported into Access). So there are several Address
fields: Business address, home address, other address, etc.) I want to print
those that are filled in, but not print lines when the other address fields
are empty. Once I do the =Trim and add the ", " in there, it prints lines
with the commas , because I'm "forcing" them. How can I get it to eliminate
that line in the report if the fields listed in that text box are null?

Thanks. Kathy.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with the TRIM command 3
Two Trims in one Text Box 1
Trimming fields that don't have data 2
New Line in a Trim Statement 1
How do I suppress blank lines in reports 1
How to Suppress Report Line Containing Label But With Blank/Null D 2
How do I trim an input field 9
How to suppress blank yes/no fields 0

Back
Top