Combining Address Fields

[Brian]

I have a contact database that includes the following
fields:

[FirstName]
[LastName]
[CompanyName]
[Address]
[Address1]
[Address2]
[Address3]
[City]
[StateOrProvince]
[PostalCode]

I want to create a field in a query that will combine
these fields so that this new field will look like an
address label.

Any thoughts?

 

[Sidney Linkers]

Hi Brain,

You have the same question as BFB or you are BFB!

I use a function MergeWith() to accomplish such task.

strAddressLabel=MergeWith(MergeWith(MergeWith(MergeWith(MergeWith(MergeWith(
MergeWith(MergeWith(MergeWith(MergeWith([FirstName],[LastName], " " ),
[CompanyName], chr(13)+chr(10)),
chr(13)+chr(10)),[Address],chr(10)+chr(10)),
[Address1],chr(13)+chr(10)),[Address2],chr(13)+chr(10)),
[Address3],chr(13)+chr(10)), [PostalCode],chr(13)+chr(10)),[City],"
"),[StateOrProvince],chr(13)+chr(10))

You'll have to test above assignment, because i didn't! Below functions are
tested!
If you want to use this in a query just replace the "=" with ":" and
"strAddressLabel" with "AddressLabel".
To understand what is happening just read the above assignment from the last
MergeWith() to the first MergeWith()

Good luck!

Function MergeWith(strFirst As String, strSecond As String, strBetween As
String) As String
If IsBlank(strSecond) Then
MergeWith = strFirst
ElseIf IsBlank(strFirst) Then
MergeWith = strSecond
Else
MergeWith = strFirst & strBetween & strSecond
End If
End Function

Function IsBlank(strString As Variant) As Boolean
If IsMissing(strString) Then
IsBlank = True
ElseIf strString = Null Then
IsBlank = True
ElseIf IsNull(strString) Then
IsBlank = True
ElseIf IsEmpty(strString) Then
IsBlank = True
ElseIf Len(strString) = 0 Then
IsBlank = True
ElseIf Alltrim(strString & "") = "" Then
IsBlank = True
Else
IsBlank = False
End If
End Function

Function Alltrim(strString As String) As String
Alltrim = LTrim(RTrim(strString))
End Function

Sid.