I am creating a SSRS Report to produce some address labels. I have a SELECT statement as follows:
SELECT DISTINCT
AddressLink.intAddressID, Addresses.txtLabelSalutation, Addresses.txtAddress1, Addresses.txtAddress2, Addresses.txtAddress3, Addresses.txtTown,
Addresses.txtCounty, Addresses.txtCountry, Addresses.txtPostcode, Addresses.txtRelationType, Pupils.intNCYear, Pupils.txtSchoolID, Pupils.txtForename,
Pupils.txtSurname
FROM TblPupilManagementAddresses AS Addresses INNER JOIN
TblPupilManagementAddressLink AS AddressLink ON Addresses.TblPupilManagementAddressesID = AddressLink.intAddressID LEFT OUTER JOIN
TblPupilManagementPupils AS Pupils ON AddressLink.txtSchoolID = Pupils.txtSchoolID
WHERE (Pupils.intSystemStatus = 1) AND (Addresses.txtAddress1 <> '') AND (Addresses.txtRelationType <> 'OLD Billing') AND (Addresses.intJustContact <> 1)
And within the report itself I am using the following expression to produce the addresses:
=Fields!txtLabelSalutation.Value & vbCRLF &
Fields!txtAddress1.Value & vbCRLF &
IIF(
Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value, nothing) & " " &
IIF (
Fields!txtAddress3.Value<> "", Fields!txtAddress3.Value, nothing) & vbCRLF & Fields!txtTown.Value
& vbCRLF &
IIF(
Fields!txtCounty.Value <> "", Fields!txtCounty.Value, nothing) & vbCRLF &
IIF(
Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value, nothing) & vbCRLF & Fields!txtPostcode.Value
My issue is that this expression is producing blank lines for fields that have no data, so I am getting a format like:
Mr & Mrs Smith 22 Somewhere Street Somewhere Town SS48 9TQ
How can I concatenate rows together if there are blanks so the address appears in one clean block? I am sure I need to amend my expression, but I am not sure how. I need it to look like:
Mr & Mrs Smith 22 Somewhere Street Somewhere Town SS48 9TQ
I have already tried amending the SQL statement using a CAST, however this then removes the DISTINCT clause and this part is vital. I also tried by grouping with the cast but that also failed.
Best Answer
With a bit of help I have solved this, but thought I would post the answer in case it helps someone else.
I have changed the expression to read:
My problem was where I was including the line break within the statement.