Adding a carriage return

[Guest]

Hi,
I'm building mailing labels and I'm wondering how to add a "carriage return"
so the labels have:

MailingAddress1
OptAddress1 (if optional address: on a separate line; if no optional
address: no blank line).

MailingAddress1
OptAddress1
City, State Zip

Or
MailingAddress1
City, State Zip

Here's the function:

Function DetermineAddress _
(AddressFlag As Variant, MailingAddress1 As Variant, OptAddress1
As Variant, MailingAddress2 As Variant, OptAddress2 As Variant) As String

Select Case AddressFlag
Case 1
DetermineAddress = MailingAddress1 + " " + Nz(OptAddress1, "")
....

Here's the label query snip:
DetermineAddress([AddressFlag],[MailingAddress1],[OptAddress1],[MailingAddress2],[OptAddress2]) AS Address

Here's the label formatting snip:
& Trim(LK.Address) & Chr(13) & Chr(10)

But by this time, I've concatenated Address into one line. I'd appreciate
your suggestions. Thanks.

 

[SusanV]

Where you want the carriage return use vbCrLf.

 

[BruceM]

You can use vbCrLf in VBA rather than Chr(13) & Chr(10). However, you may
be overcomplicating this. If you use + rather than & as the concatenation
operator, if any part of the expression is null the entire expression
evaluates to null. You can use this to your advantage. In a query you
could add a Full Address field. Note that you need to use Chr(13) & Chr(10)
in this case, since it is SQL rather thatn VBA.

FullAddress: FullName & MailingAddress1 & Chr(13) & Chr(10) & _
(OptAddress1 + Chr(13) + Chr(10)) & _
City & ", " & State & " " & Zip

If OptAddress1 is null, the entire expression within parentheses, including
Chr(13) + Chr(10), evaluates to null. You can extend the expression to add
more fields such as MailingAddress2 and OptAddress2. Note that the
underscores are an attempt to get this to read correctly in a newsreader
window, and are not part of the SQL.

In a form or report you could do something similar. The FullAddress text
box is named txtFullAddress.

Me.txtFullAddress = FullName & MailingAddress1 & _
vbCrLf & (OptAddress1 + vbCrLf) & _
City & ", " & State & " " & Zip

 

[Guest]

Bruce,
Thanks for your post- it was very helpful. And I'm very close...
I created a separate function to determine OptAddress. But I'm still having
a few spacing issues I'm hoping you can help me with. I seem to be getting a
blank line for OptAddress. Way back, someone helped me created very
complicated embedded IIf statements. I'd love to streamline if possible.

Here's a snip of the Members query (I left off the function parameters-
functions work correctly):
SELECT nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], DetermineCompanyName(parameters) AS [Company Name],
DetermineAddress(parameters) AS Address, DetermineOptAddress(parameters) AS
OptAddress, DetermineCity(parameters]) AS City, DetermineState(parameters])
AS State, DeterminePostalCode(parameters) AS PostalCode...

Then the query to form the labels:
SELECT (IIf(Len(Trim(MK.[Company Name]) & "")=0,IIf(Len(Trim(MK.[SO Name]) &
"")=0,Trim(MK.[Member Name]) & Chr(13) & Chr(10) & Trim(MK.Address) & Chr(13)
& Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City & ", " & UCase(
MK.State) & " " & MK.PostalCode) & Chr(13) & Chr(10),Trim(MK.[Member Name]) &
Chr(13) & Chr(10) & Trim(MK.[SO Name]) & Chr(13) & Chr(10) & Trim(MK.Address)
& Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City & ",
" & MK.State & " " & MK.PostalCode & " " &MK.Country)),Trim(MK.[Member
Name]) & Chr(13) & Chr(10) & Trim(MK.[Company Name]) & Chr(13) & Chr(10) &
Trim(MK.Address) & Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) &
Trim(MK.City & ", " & UCase( MK.State) & " " & MK.PostalCode & " " &
Country))) AS MyLabelContents
FROM [Members] AS MK

Then the label report lists MyLabelContents from above.

Please tell me it doesn't need to be that difficult!

Ultimately I'd like:

ABC Company
Attn: Barbara
1234 Wall St.
Suite 211
Boise, ID 84756

or

John Smith
Sally Smith
123 Terrace Lane
Boise, ID 85746

Or
Sarah Johnson
24 S. Richards
Boise, ID 84756

sorry to dump so much sql on you- just wanted you to see where I stand now...

thanks!

You can use vbCrLf in VBA rather than Chr(13) & Chr(10). However, you may
be overcomplicating this. If you use + rather than & as the concatenation
operator, if any part of the expression is null the entire expression
evaluates to null. You can use this to your advantage. In a query you
could add a Full Address field. Note that you need to use Chr(13) & Chr(10)
in this case, since it is SQL rather thatn VBA.

FullAddress: FullName & MailingAddress1 & Chr(13) & Chr(10) & _
(OptAddress1 + Chr(13) + Chr(10)) & _
City & ", " & State & " " & Zip

If OptAddress1 is null, the entire expression within parentheses, including
Chr(13) + Chr(10), evaluates to null. You can extend the expression to add
more fields such as MailingAddress2 and OptAddress2. Note that the
underscores are an attempt to get this to read correctly in a newsreader
window, and are not part of the SQL.

In a form or report you could do something similar. The FullAddress text
box is named txtFullAddress.

Me.txtFullAddress = FullName & MailingAddress1 & _
vbCrLf & (OptAddress1 + vbCrLf) & _
City & ", " & State & " " & Zip

Hi,
I'm building mailing labels and I'm wondering how to add a "carriage
return"
so the labels have:

MailingAddress1
OptAddress1 (if optional address: on a separate line; if no optional
address: no blank line).

MailingAddress1
OptAddress1
City, State Zip

Or
MailingAddress1
City, State Zip

Here's the function:

Function DetermineAddress _
(AddressFlag As Variant, MailingAddress1 As Variant,
OptAddress1
As Variant, MailingAddress2 As Variant, OptAddress2 As Variant) As String

Select Case AddressFlag
Case 1
DetermineAddress = MailingAddress1 + " " + Nz(OptAddress1, "")
...

Here's the label query snip:
DetermineAddress([AddressFlag],[MailingAddress1],[OptAddress1],[MailingAddress2],[OptAddress2])
AS Address

Here's the label formatting snip:
& Trim(LK.Address) & Chr(13) & Chr(10)

But by this time, I've concatenated Address into one line. I'd appreciate
your suggestions. Thanks.

 

[Guest]

First, WHY are you attempting to generate a mailing label directly from a
query? Write your query, then create a report from that query which prints
the labels. Add the fields you want to the report, specify the label type,
save the report, and have at it. To me (and I could be absoultely wrong here;
wouldn't be the first time), you're attempting to build an airplane with a
jeweler's screwdriver. It can probably be done, but why go through all the
abuse?

;^)

Bruce,
Thanks for your post- it was very helpful. And I'm very close...
I created a separate function to determine OptAddress. But I'm still having
a few spacing issues I'm hoping you can help me with. I seem to be getting a
blank line for OptAddress. Way back, someone helped me created very
complicated embedded IIf statements. I'd love to streamline if possible.

Here's a snip of the Members query (I left off the function parameters-
functions work correctly):
SELECT nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], DetermineCompanyName(parameters) AS [Company Name],
DetermineAddress(parameters) AS Address, DetermineOptAddress(parameters) AS
OptAddress, DetermineCity(parameters]) AS City, DetermineState(parameters])
AS State, DeterminePostalCode(parameters) AS PostalCode...

Then the query to form the labels:
SELECT (IIf(Len(Trim(MK.[Company Name]) & "")=0,IIf(Len(Trim(MK.[SO Name]) &
"")=0,Trim(MK.[Member Name]) & Chr(13) & Chr(10) & Trim(MK.Address) & Chr(13)
& Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City & ", " & UCase(
MK.State) & " " & MK.PostalCode) & Chr(13) & Chr(10),Trim(MK.[Member Name]) &
Chr(13) & Chr(10) & Trim(MK.[SO Name]) & Chr(13) & Chr(10) & Trim(MK.Address)
& Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City & ",
" & MK.State & " " & MK.PostalCode & " " &MK.Country)),Trim(MK.[Member
Name]) & Chr(13) & Chr(10) & Trim(MK.[Company Name]) & Chr(13) & Chr(10) &
Trim(MK.Address) & Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) &
Trim(MK.City & ", " & UCase( MK.State) & " " & MK.PostalCode & " " &
Country))) AS MyLabelContents
FROM [Members] AS MK

Then the label report lists MyLabelContents from above.

Please tell me it doesn't need to be that difficult!

Ultimately I'd like:

ABC Company
Attn: Barbara
1234 Wall St.
Suite 211
Boise, ID 84756

or

John Smith
Sally Smith
123 Terrace Lane
Boise, ID 85746

Or
Sarah Johnson
24 S. Richards
Boise, ID 84756

sorry to dump so much sql on you- just wanted you to see where I stand now...

thanks!

You can use vbCrLf in VBA rather than Chr(13) & Chr(10). However, you may
be overcomplicating this. If you use + rather than & as the concatenation
operator, if any part of the expression is null the entire expression
evaluates to null. You can use this to your advantage. In a query you
could add a Full Address field. Note that you need to use Chr(13) & Chr(10)
in this case, since it is SQL rather thatn VBA.

FullAddress: FullName & MailingAddress1 & Chr(13) & Chr(10) & _
(OptAddress1 + Chr(13) + Chr(10)) & _
City & ", " & State & " " & Zip

If OptAddress1 is null, the entire expression within parentheses, including
Chr(13) + Chr(10), evaluates to null. You can extend the expression to add
more fields such as MailingAddress2 and OptAddress2. Note that the
underscores are an attempt to get this to read correctly in a newsreader
window, and are not part of the SQL.

In a form or report you could do something similar. The FullAddress text
box is named txtFullAddress.

Me.txtFullAddress = FullName & MailingAddress1 & _
vbCrLf & (OptAddress1 + vbCrLf) & _
City & ", " & State & " " & Zip

Hi,
I'm building mailing labels and I'm wondering how to add a "carriage
return"
so the labels have:

MailingAddress1
OptAddress1 (if optional address: on a separate line; if no optional
address: no blank line).

MailingAddress1
OptAddress1
City, State Zip

Or
MailingAddress1
City, State Zip

Here's the function:

Function DetermineAddress _
(AddressFlag As Variant, MailingAddress1 As Variant,
OptAddress1
As Variant, MailingAddress2 As Variant, OptAddress2 As Variant) As String

Select Case AddressFlag
Case 1
DetermineAddress = MailingAddress1 + " " + Nz(OptAddress1, "")
...

Here's the label query snip:
DetermineAddress([AddressFlag],[MailingAddress1],[OptAddress1],[MailingAddress2],[OptAddress2])
AS Address

Here's the label formatting snip:
& Trim(LK.Address) & Chr(13) & Chr(10)

But by this time, I've concatenated Address into one line. I'd appreciate
your suggestions. Thanks.

 

[BruceM]

Stephanie, I think you can do everythng you need with concatenation. It's
getting near the end of my day, so I can't really spend much time looking at
this now, but I will say there is more going on than I thought at first.
Still, I don't see where the functions are streamlining anything. If you
had asked about combining fields into an address I would have made the
suggestion I did. I have to say I can't see without studying the functions
just what is going on here. I have your assurance that the functions work
correctly, but I can't fill in the missing pieces in my mind. I will
revisit the thread on Monday, but perhaps somebody will have responded in
the meantime.

Bruce,
Thanks for your post- it was very helpful. And I'm very close...
I created a separate function to determine OptAddress. But I'm still
having
a few spacing issues I'm hoping you can help me with. I seem to be getting
a
blank line for OptAddress. Way back, someone helped me created very
complicated embedded IIf statements. I'd love to streamline if possible.

Here's a snip of the Members query (I left off the function parameters-
functions work correctly):
SELECT nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm]
FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], DetermineCompanyName(parameters) AS [Company Name],
DetermineAddress(parameters) AS Address, DetermineOptAddress(parameters)
AS
OptAddress, DetermineCity(parameters]) AS City,
DetermineState(parameters])
AS State, DeterminePostalCode(parameters) AS PostalCode...

Then the query to form the labels:
SELECT (IIf(Len(Trim(MK.[Company Name]) & "")=0,IIf(Len(Trim(MK.[SO Name])
&
"")=0,Trim(MK.[Member Name]) & Chr(13) & Chr(10) & Trim(MK.Address) &
Chr(13)
& Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City & ", " &
UCase(
MK.State) & " " & MK.PostalCode) & Chr(13) & Chr(10),Trim(MK.[Member
Name]) &
Chr(13) & Chr(10) & Trim(MK.[SO Name]) & Chr(13) & Chr(10) &
Trim(MK.Address)
& Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City &
",
" & MK.State & " " & MK.PostalCode & " " &MK.Country)),Trim(MK.[Member
Name]) & Chr(13) & Chr(10) & Trim(MK.[Company Name]) & Chr(13) & Chr(10) &
Trim(MK.Address) & Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) &
Trim(MK.City & ", " & UCase( MK.State) & " " & MK.PostalCode & " " &
Country))) AS MyLabelContents
FROM [Members] AS MK

Then the label report lists MyLabelContents from above.

Please tell me it doesn't need to be that difficult!

Ultimately I'd like:

ABC Company
Attn: Barbara
1234 Wall St.
Suite 211
Boise, ID 84756

or

John Smith
Sally Smith
123 Terrace Lane
Boise, ID 85746

Or
Sarah Johnson
24 S. Richards
Boise, ID 84756

sorry to dump so much sql on you- just wanted you to see where I stand
now...

thanks!

You can use vbCrLf in VBA rather than Chr(13) & Chr(10). However, you
may
be overcomplicating this. If you use + rather than & as the
concatenation
operator, if any part of the expression is null the entire expression
evaluates to null. You can use this to your advantage. In a query you
could add a Full Address field. Note that you need to use Chr(13) &
Chr(10)
in this case, since it is SQL rather thatn VBA.

FullAddress: FullName & MailingAddress1 & Chr(13) & Chr(10) & _
(OptAddress1 + Chr(13) + Chr(10)) & _
City & ", " & State & " " & Zip

If OptAddress1 is null, the entire expression within parentheses,
including
Chr(13) + Chr(10), evaluates to null. You can extend the expression to
add
more fields such as MailingAddress2 and OptAddress2. Note that the
underscores are an attempt to get this to read correctly in a newsreader
window, and are not part of the SQL.

In a form or report you could do something similar. The FullAddress text
box is named txtFullAddress.

Me.txtFullAddress = FullName & MailingAddress1 & _
vbCrLf & (OptAddress1 + vbCrLf) & _
City & ", " & State & " " & Zip

Hi,
I'm building mailing labels and I'm wondering how to add a "carriage
return"
so the labels have:

MailingAddress1
OptAddress1 (if optional address: on a separate line; if no optional
address: no blank line).

MailingAddress1
OptAddress1
City, State Zip

Or
MailingAddress1
City, State Zip

Here's the function:

Function DetermineAddress _
(AddressFlag As Variant, MailingAddress1 As Variant,
OptAddress1
As Variant, MailingAddress2 As Variant, OptAddress2 As Variant) As
String

Select Case AddressFlag
Case 1
DetermineAddress = MailingAddress1 + " " + Nz(OptAddress1,
"")
...

Here's the label query snip:
DetermineAddress([AddressFlag],[MailingAddress1],[OptAddress1],[MailingAddress2],[OptAddress2])
AS Address

Here's the label formatting snip:
& Trim(LK.Address) & Chr(13) & Chr(10)

But by this time, I've concatenated Address into one line. I'd
appreciate
your suggestions. Thanks.

 

[Guest]

Thanks! Have a good weekend!

Stephanie, I think you can do everythng you need with concatenation. It's
getting near the end of my day, so I can't really spend much time looking at
this now, but I will say there is more going on than I thought at first.
Still, I don't see where the functions are streamlining anything. If you
had asked about combining fields into an address I would have made the
suggestion I did. I have to say I can't see without studying the functions
just what is going on here. I have your assurance that the functions work
correctly, but I can't fill in the missing pieces in my mind. I will
revisit the thread on Monday, but perhaps somebody will have responded in
the meantime.

Bruce,
Thanks for your post- it was very helpful. And I'm very close...
I created a separate function to determine OptAddress. But I'm still
having
a few spacing issues I'm hoping you can help me with. I seem to be getting
a
blank line for OptAddress. Way back, someone helped me created very
complicated embedded IIf statements. I'd love to streamline if possible.

Here's a snip of the Members query (I left off the function parameters-
functions work correctly):
SELECT nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm]
FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], DetermineCompanyName(parameters) AS [Company Name],
DetermineAddress(parameters) AS Address, DetermineOptAddress(parameters)
AS
OptAddress, DetermineCity(parameters]) AS City,
DetermineState(parameters])
AS State, DeterminePostalCode(parameters) AS PostalCode...

Then the query to form the labels:
SELECT (IIf(Len(Trim(MK.[Company Name]) & "")=0,IIf(Len(Trim(MK.[SO Name])
&
"")=0,Trim(MK.[Member Name]) & Chr(13) & Chr(10) & Trim(MK.Address) &
Chr(13)
& Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City & ", " &
UCase(
MK.State) & " " & MK.PostalCode) & Chr(13) & Chr(10),Trim(MK.[Member
Name]) &
Chr(13) & Chr(10) & Trim(MK.[SO Name]) & Chr(13) & Chr(10) &
Trim(MK.Address)
& Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) & Trim(MK.City &
",
" & MK.State & " " & MK.PostalCode & " " &MK.Country)),Trim(MK.[Member
Name]) & Chr(13) & Chr(10) & Trim(MK.[Company Name]) & Chr(13) & Chr(10) &
Trim(MK.Address) & Chr(13) & Chr(10) & (OptAddress + Chr(13) + Chr(10)) &
Trim(MK.City & ", " & UCase( MK.State) & " " & MK.PostalCode & " " &
Country))) AS MyLabelContents
FROM [Members] AS MK

Then the label report lists MyLabelContents from above.

Please tell me it doesn't need to be that difficult!

Ultimately I'd like:

ABC Company
Attn: Barbara
1234 Wall St.
Suite 211
Boise, ID 84756

or

John Smith
Sally Smith
123 Terrace Lane
Boise, ID 85746

Or
Sarah Johnson
24 S. Richards
Boise, ID 84756

sorry to dump so much sql on you- just wanted you to see where I stand
now...

thanks!

You can use vbCrLf in VBA rather than Chr(13) & Chr(10). However, you
may
be overcomplicating this. If you use + rather than & as the
concatenation
operator, if any part of the expression is null the entire expression
evaluates to null. You can use this to your advantage. In a query you
could add a Full Address field. Note that you need to use Chr(13) &
Chr(10)
in this case, since it is SQL rather thatn VBA.

FullAddress: FullName & MailingAddress1 & Chr(13) & Chr(10) & _
(OptAddress1 + Chr(13) + Chr(10)) & _
City & ", " & State & " " & Zip

If OptAddress1 is null, the entire expression within parentheses,
including
Chr(13) + Chr(10), evaluates to null. You can extend the expression to
add
more fields such as MailingAddress2 and OptAddress2. Note that the
underscores are an attempt to get this to read correctly in a newsreader
window, and are not part of the SQL.

In a form or report you could do something similar. The FullAddress text
box is named txtFullAddress.

Me.txtFullAddress = FullName & MailingAddress1 & _
vbCrLf & (OptAddress1 + vbCrLf) & _
City & ", " & State & " " & Zip


Hi,
I'm building mailing labels and I'm wondering how to add a "carriage
return"
so the labels have:

MailingAddress1
OptAddress1 (if optional address: on a separate line; if no optional
address: no blank line).

MailingAddress1
OptAddress1
City, State Zip

Or
MailingAddress1
City, State Zip

Here's the function:

Function DetermineAddress _
(AddressFlag As Variant, MailingAddress1 As Variant,
OptAddress1
As Variant, MailingAddress2 As Variant, OptAddress2 As Variant) As
String

Select Case AddressFlag
Case 1
DetermineAddress = MailingAddress1 + " " + Nz(OptAddress1,
"")
...

Here's the label query snip:
DetermineAddress([AddressFlag],[MailingAddress1],[OptAddress1],[MailingAddress2],[OptAddress2])
AS Address

Here's the label formatting snip:
& Trim(LK.Address) & Chr(13) & Chr(10)

But by this time, I've concatenated Address into one line. I'd
appreciate
your suggestions. Thanks.