Conditional Concatenating Text Fields in a Report

[gcbeldar]

Good Morning,

Have Database Fields; Address1, Address2, Address3, Address4.

Designed a Report with One Text Box linked as;
=[Address1] & "," & " " & [Address2] & "," & " " & [Address3] & "," & " " & [Address4]

Now wanted 2 requirements;
1. If [AddressX] is blank, char "," should not print, which is printing with the above code.
2. How print "On the next line after printing [Address1] and [Address2]" ?



Thanks in Advance.

[June7]

Try something like:

=[Address1] & ", " + [Address2] & Chr(13) & Chr(10) & ", " + [Address3] & ", " + [Address4]

Otherwise, build a custom VBA function to construct the string.

[ArviLaanemets]

Expanding June7's formula
=[Address1] & ", " + [Address2] & Chr(13) & Chr(10) & Iif(Nz( [Address3],"")="","",", ") & Nz([Address3],"") & ", " + [Address4]

[gcbeldar]

First Thanks to ALL,

[gcbeldar]

Expanding June7's formula
=[Address1] & ", " + [Address2] & Chr(13) & Chr(10) & Iif(Nz( [Address3],"")="","",", ") & Nz([Address3],"") & ", " + [Address4]

Dear Sir,

Thanks and this code is working as per my requirement,
=[HAddress1] & IIf([HAddress1],", "," ") & Chr(13) & Chr(10) & [HAddress2] & IIf([HAddress2],", "," ")