Solved Concatenating addresses on a report

[debsamguru]

I'm trying to concatenate address lines on a report so that empty lines don't show up. I've searched the forum and found a thread that recommends doing this:

=[InvoiceAddr1]+Chr(13) & Chr(10)+[InvoiceAddr2]+Chr(13) & Chr(10)+[InvoiceAddr3]+Chr(13) & Chr(10)+[InvoiceAddr4]+Chr(13) & Chr(10)+[InvoiceAddr5]+Chr(13) & Chr(10)+[InvoicePostcode]+Chr(13) & Chr(10)+[InvoiceCountry]

I have put this code in the Control Source of the first Text Box and removed all of the other text boxes and made sure that Can Grow/Can Shrink are set to 'Yes'.

However, all I get is #Type! The Text Format is set to Plain Text and all of the fields are defined as Short Text fields.

Does anyone have any ideas please?

 

[plog]

1. The concatenate operator in Access is & not +.

2. Don't try and eat your elephant in one bite. Which of those 20 things you are concatenating together is the issue? Wipe out 19 and just leave [InvoiceAddr1] in there. If that's not the issue add Chr(13) to it. If that doesn't break it add Chr(10), if that doesn't break it add [InvoiceAddr2] if that doesn't break it...

 

[pbaldy]

1. The concatenate operator in Access is & not +.

Both can be used to concatenate, and the trick being attempted here is that + will propagate Null while & will not.

I'd start small as plog recommended, and use parentheses around the + concatenations so you aren't mixing:

=FirstAddress & (PotentialNull + Chr(10))

 

[debsamguru]

Thanks. I'll try that tomorrow.

 

[arnelgp]

you can also create a function in a Module to perform the concatenation:

' arnelgp
Public Function fnConcatFields(ByVal separator As String, ParamArray fields() As Variant) As String
Dim ret As String, tmp As String
Dim i As Integer
For i = 0 To UBound(fields)
    tmp = Trim$(fields(i) & "")
    If Len(tmp) <> 0 Then
        ret = ret & tmp & separator
    End If
Next
If Len(ret) <> 0 Then
    ret = Left$(ret, Len(ret) - 2)
End If
fnConcatFields =

then on a Textbox on your Report:

=fnConcatFields(Chr(13)+Chr(10),[InvoiceAddr1],[InvoiceAddr2],[InvoiceAddr3],[InvoiceAddr4],[InvoiceAddr5],[InvoicePostcode],[InvoiceCountry])

 

[Gasman]

Knowing that some members just copy the code and not know what it does......

fnConcatFields = ret
End Function

 

[debsamguru]

Thank you all for your help. I did, in the end, create functions, but for anyone who doesn't want to create a function, the reason it failed was because of missing parentheses around the Chr code. The code worked when I put them in as follows: -

=[InvoiceAddr1]+(Chr(13) & Chr(10))+[InvoiceAddr2]+(Chr(13) & Chr(10))+[InvoiceAddr3]+(Chr(13) & Chr(10))+[InvoiceAddr4]++(Chr(13) & Chr(10))+[InvoicePostcode]+(Chr(13) & Chr(10))+[InvoiceCountry]

Also, nothing was output at all when the blank field (in this case InvoiceAddr5) was added to the mix which is why I went for the Function approach.

 

[NeilT123]

I use this in a text box which removes blank fields.

=IIf(Nz(Trim([AccountName]),"")<>"",[AccountName] & Chr(13) & Chr(10)) & IIf(Nz(Trim([Address1]),"")<>"",[Address1] & Chr(13) & Chr(10)) & IIf(Nz(Trim([Address2]),"")<>"",[Address2] & Chr(13) & Chr(10)) & IIf(Nz(Trim([City]),"")<>"",[City] & Chr(13) & Chr(10)) & IIf(Nz(Trim([County]),"")<>"",[County] & Chr(13) & Chr(10)) & [PostCode]