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

[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.

 

[Jeff Boyce]

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/

 

[Guest]

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)

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/

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.

 

[Guest]

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

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/

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.