Combining Fields in a query

B

BFB

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?
 
B

bob Sullivan

You might want to do this in a report. If you choose
Create a New Report and use the wizard, one of your
choices will be mailing labels. This will give you the
format you want.

Bob Sullivan
Springhouse Computer School
 
S

Sidney Linkers

Hi 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

Sid.
 
S

Sidney Linkers

Hi Again,

Forgot to include the Alltrim() function!

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

Sid.

Sidney Linkers said:
Hi 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

Sid.

BFB said:
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?
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Sidney, why not use the TRIM function that is built into VBA? It should do
exactly what your user-defined AllTrim function does.

Sidney said:
Hi Again,

Forgot to include the Alltrim() function!

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

Sid.

Sidney Linkers said:
Hi 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

Sid.

BFB said:
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?
Click to expand...
Click to expand...
Click to expand...
 
S

Sidney Linkers

Hi John,

I thought Acc97 only has Ltrim() and RTrim()? Acc2000 i'm not completly
familair with (and offcourse lazyness, if something is not broken why fix
it?).

Sid.

John Spencer (MVP) said:
Sidney, why not use the TRIM function that is built into VBA? It should do
exactly what your user-defined AllTrim function does.

Sidney said:
Hi Again,

Forgot to include the Alltrim() function!

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

Sid.

Sidney Linkers said:
Hi BFB,

I use a function MergeWith() to accomplish such task.
Click to expand...
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
Click to expand...
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
Click to expand...
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

Sid.

"BFB" <[email protected]> schreef in bericht
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?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Sidney,

No, Access 97 has the Trim function also. I was just curious. I thought maybe
you had done some testing and found your method was more efficient or effective
(faster or more accurate) then using TRIM.

No criticism intended; just seeking enlightenment.

Sidney said:
Hi John,

I thought Acc97 only has Ltrim() and RTrim()? Acc2000 i'm not completly
familair with (and offcourse lazyness, if something is not broken why fix
it?).

Sid.

John Spencer (MVP) said:
Sidney, why not use the TRIM function that is built into VBA? It should do
exactly what your user-defined AllTrim function does.

Sidney said:
Hi Again,

Forgot to include the Alltrim() function!

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

Sid.

"Sidney Linkers" <[email protected]> schreef in bericht
Hi 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

Sid.

"BFB" <[email protected]> schreef in bericht
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?
Click to expand...
Click to expand...
Click to expand...
 
S

Sidney Linkers

Hi John,

Thanks for the suggestion. I think where the use of Alltrim() came from. I
have been developping in CLIPPER for a couple of years and have build
several modules with standard functions. Somehow this one has slipped
through....hehe.

Sid.

John Spencer (MVP) said:
Sidney,

No, Access 97 has the Trim function also. I was just curious. I thought maybe
you had done some testing and found your method was more efficient or effective
(faster or more accurate) then using TRIM.

No criticism intended; just seeking enlightenment.

Sidney said:
Hi John,

I thought Acc97 only has Ltrim() and RTrim()? Acc2000 i'm not completly
familair with (and offcourse lazyness, if something is not broken why fix
it?).

Sid.

John Spencer (MVP) said:
Sidney, why not use the TRIM function that is built into VBA? It
Click to expand...
should
do
exactly what your user-defined AllTrim function does.

Sidney Linkers wrote:

Hi Again,

Forgot to include the Alltrim() function!

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

Sid.

"Sidney Linkers" <[email protected]> schreef in bericht
Hi BFB,

I use a function MergeWith() to accomplish such task.
Click to expand...
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
Click to expand...
from
the
last
MergeWith() to the first MergeWith()

Good luck!

Function MergeWith(strFirst As String, strSecond As String,
Click to expand...
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

Sid.

"BFB" <[email protected]> schreef in bericht
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining Address Fields 1
table formatting not carrying to calculated field in query 4
Increment field by 1 on insert Query 2
Need If-Then-Else or Similar Construct in Test for Null Value 10
sql union self-join syntax 27
Count Distinct Values In Several Columns or Fields 4
INSERT INTO statement 5
Goup query 3

Top