Trouble with '+' Concatenation

[Guest]

I'm trying to build the complete address from individual fields like
'Address1', 'Address2' etc. from tblAddresses. The idea behind the +
concatenation is to avoid the extra commas for null fields in the Address
Block.

The following code gives me an "Invalid Use of Null" message. Can someone
please point out where I'm doing wrong ?

Public Function FullAddress(strID As String, strAddressType As String) As
String
On Error GoTo ErrHandle

Dim AddBlock As String
Dim strWhere As String

strWhere = "[ID] = '" & strID & "'"
strWhere = strWhere & " AND [AddressType] = '" & strAddressType & "'"

AddBlock = DLookup("Address1", "tblAddresses", strWhere)
AddBlock = AddBlock + ", " + DLookup("Address2", "tblAddresses", strWhere)
AddBlock = AddBlock + ", " + DLookup("Address3", "tblAddresses", strWhere)
AddBlock = AddBlock + ", " + DLookup("Address4", "tblAddresses", strWhere)
AddBlock = AddBlock + ", " + DLookup("Address5", "tblAddresses", strWhere)
AddBlock = AddBlock + ", " + DLookup("Address6", "tblAddresses", strWhere)

FullAddress = AddBlock

ExitHandle:
Exit Function

ErrHandle:
LogError "FullAddress" & modName, err, Error
Resume ExitHandle

End Function

 

[Guest]

Hi guys,
Sorry for my haste in posting this thread. Should have done some homework
myself. I've replaced the first '+' with '&' and it works. The code now looks
like this:

AddBlock = DLookup("Address1", "tblAddresses", strWhere)
AddBlock = AddBlock & ", " + DLookup("Address2", "tblAddresses", strWhere)

Someone said, "An expert is one who has already done every conceivable
mistake in the area of his expertise." Maybe, I'm on my way, though far from
destiny.

;-) ;-) ;-)

 

[Brendan Reynolds]

You're attempting to assign Null values to a String variable. (", " + a Null
value will return Null). You can't do that, you can only assign a Null value
to a Variant. To make this code work, you'd need to change the definition of
AddBlock from String to Variant ...

'Dim AddBlock As String
Dim AddBlock As Variant

 

[Guest]

Thank you very much for helping me get the facts right.

 

[Tim Ferguson]

I'm trying to build the complete address from individual fields like
'Address1', 'Address2' etc. from tblAddresses. The idea behind the +
concatenation is to avoid the extra commas for null fields in the
Address Block.

(Address1 + vbCrLf) & _
(Address2 + vbCrLf) & _
(Address3 + vbCrLf) & _
PostCode


Sorry: just replace the vbCrLf with ", " -- must read the question more
carefully...


HTH

Tim F