formating addresses properly 1

[gojohnnygogogogo]

hello,
hope this is an easy one to solve.

I have address fields,
Address1, Address2, Address3, country, postcode

showing them on a report for labels, but the labels have gaps where no data is in some of the fields.

how can I removed these blanks, and generally make the labels look good.

thank you in advance.

 

[SgtJarrow]

The below code will work....it using the propogation of Null for its logic....

Public Function RemoveEmptyAddresses(strAddress1 As Variant, Optional strAddress2 As Variant = Null, _
Optional strAddress3 As Variant = Null, Optional strCountry As Variant = Null, _
Optional strPostCode As Variant = Null) As String

RemoveEmptyAddresses = (strAddress1 + ", &quot & (strAddress2 + ", &quot & (strAddress3 + ", &quot _
& (strCountry + ", &quot & (strPostCode + " &quot

End Function Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[gojohnnygogogogo]

thank you robert,, that code will come in handy.
but how would I use it ?
do I create a text box on my report and make the source :

=removeemptyaddresses()

?

 

[SgtJarrow]

You code do that......but it would be a bit more than that.

You would need to include the fileds on the report, set the .visible properties to No and the use:

=removeemptyaddresses([address1fieldname],[address2fieldname], [address3fieldname], [countryfieldname], [postcodefieldname])

But me personally, I would do this in the query for the report and then just display the field on the report....

In the query make sure the above five fields are included. Add a field at the end of the query with the following in the field line:

MyAddress: =removeemptyaddresses([address1fieldname],[address2fieldname], [address3fieldname], [countryfieldname], [postcodefieldname])


Then in the report, you only need to include the field called MyAddress and you should be there.....if you want to see examples, send me an email and I will put both attempts together for you.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com