Concatenation in Reports

[Tony]

Hi All,

I'm looking for a 'simple' way to concatenate fields from a report's
underlying recordset into an unbound text controls. The data set is an
address, but sometimes I will not have a street address or city. For
example, the data might look like this:

STREET CITY STATE
123 Main New York NY
345 Oak St NY
New York NY

There are two controls on the report, addressLine1 & addressLine2. Using
the above data, the addresses would be displayed like this:

addressLine1: 123 Main
addressLine2: New York, NY

addressLine1: 345 Oak St
addressLine2: NY

addressLine1: New York, NY
addressLine2:

I know I can do this if I bind controls to the street, city, and state
fields, hide them, then code addressLines 1 & 2 to display the data in the
format I want. However, I'd like to avoid putting the bound controls on the
report only to turn around and hide them.

If anyone has any suggestions, I would love to hear them.

Thanks & Ciao,

Tony

 

[Douglas J. Steele]

If you create a single column in the underlying query, you could use this in
a single text box that's set to Grow.

([Street] + Chr(13) & Chr(10)) & ([City] + ", ") & [State]

 

[Tony]

Doug,

Thanks for the reply. I came across this approach in a KB article, but the
situation I'm trying to handle is a bit different. I'm working with two
address lines and if there is no street address, I want to move the city &
state information up to the first line and leave the second blank.

Any suggestions under for that scenario?

Again, thanks for the help.

Tony

If you create a single column in the underlying query, you could use this
in a single text box that's set to Grow.

([Street] + Chr(13) & Chr(10)) & ([City] + ", ") & [State]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi All,

I'm looking for a 'simple' way to concatenate fields from a report's
underlying recordset into an unbound text controls. The data set is an
address, but sometimes I will not have a street address or city. For
example, the data might look like this:

STREET CITY STATE
123 Main New York NY
345 Oak St NY
New York NY

There are two controls on the report, addressLine1 & addressLine2. Using
the above data, the addresses would be displayed like this:

addressLine1: 123 Main
addressLine2: New York, NY

addressLine1: 345 Oak St
addressLine2: NY

addressLine1: New York, NY
addressLine2:

I know I can do this if I bind controls to the street, city, and state
fields, hide them, then code addressLines 1 & 2 to display the data in
the format I want. However, I'd like to avoid putting the bound controls
on the report only to turn around and hide them.

If anyone has any suggestions, I would love to hear them.

Thanks & Ciao,

Tony

 

[Douglas J. Steele]

Why do you need the 2nd line?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Thanks for the reply. I came across this approach in a KB article, but
the situation I'm trying to handle is a bit different. I'm working with
two address lines and if there is no street address, I want to move the
city & state information up to the first line and leave the second blank.

Any suggestions under for that scenario?

Again, thanks for the help.

Tony

If you create a single column in the underlying query, you could use this
in a single text box that's set to Grow.

([Street] + Chr(13) & Chr(10)) & ([City] + ", ") & [State]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi All,

I'm looking for a 'simple' way to concatenate fields from a report's
underlying recordset into an unbound text controls. The data set is an
address, but sometimes I will not have a street address or city. For
example, the data might look like this:

STREET CITY STATE
123 Main New York NY
345 Oak St NY
New York NY

There are two controls on the report, addressLine1 & addressLine2.
Using the above data, the addresses would be displayed like this:

addressLine1: 123 Main
addressLine2: New York, NY

addressLine1: 345 Oak St
addressLine2: NY

addressLine1: New York, NY
addressLine2:

I know I can do this if I bind controls to the street, city, and state
fields, hide them, then code addressLines 1 & 2 to display the data in
the format I want. However, I'd like to avoid putting the bound
controls on the report only to turn around and hide them.

If anyone has any suggestions, I would love to hear them.

Thanks & Ciao,

Tony

 

[Tony]

Hi Doug,

I need the second line in the event I have the address and state, but no
city. In that case, the address would go on line #1 and the state on line #
2. Does that make sense(?) & again, thanks for the assistance.

Tony

Why do you need the 2nd line?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Thanks for the reply. I came across this approach in a KB article, but
the situation I'm trying to handle is a bit different. I'm working with
two address lines and if there is no street address, I want to move the
city & state information up to the first line and leave the second blank.

Any suggestions under for that scenario?

Again, thanks for the help.

Tony

If you create a single column in the underlying query, you could use
this in a single text box that's set to Grow.

([Street] + Chr(13) & Chr(10)) & ([City] + ", ") & [State]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi All,

I'm looking for a 'simple' way to concatenate fields from a report's
underlying recordset into an unbound text controls. The data set is an
address, but sometimes I will not have a street address or city. For
example, the data might look like this:

STREET CITY STATE
123 Main New York NY
345 Oak St NY
New York NY

There are two controls on the report, addressLine1 & addressLine2.
Using the above data, the addresses would be displayed like this:

addressLine1: 123 Main
addressLine2: New York, NY

addressLine1: 345 Oak St
addressLine2: NY

addressLine1: New York, NY
addressLine2:

I know I can do this if I bind controls to the street, city, and state
fields, hide them, then code addressLines 1 & 2 to display the data in
the format I want. However, I'd like to avoid putting the bound
controls on the report only to turn around and hide them.

If anyone has any suggestions, I would love to hear them.

Thanks & Ciao,

Tony

 

[Douglas J. Steele]

The formula I gave will give you exactly what you originally asked for.

If you have Street, City, State, you'll get:

Street
City, State

If you have Street, no City, State, you'll get:

Street
State

If you have no Street, City, State, you'll get:

City, State

I'm saying have a single text box for the address, not two. Make it one line
high, but set the Can Grow property to True.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

I need the second line in the event I have the address and state, but no
city. In that case, the address would go on line #1 and the state on line
# 2. Does that make sense(?) & again, thanks for the assistance.

Tony

Why do you need the 2nd line?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Thanks for the reply. I came across this approach in a KB article, but
the situation I'm trying to handle is a bit different. I'm working with
two address lines and if there is no street address, I want to move the
city & state information up to the first line and leave the second
blank.

Any suggestions under for that scenario?

Again, thanks for the help.

Tony

If you create a single column in the underlying query, you could use
this in a single text box that's set to Grow.

([Street] + Chr(13) & Chr(10)) & ([City] + ", ") & [State]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi All,

I'm looking for a 'simple' way to concatenate fields from a report's
underlying recordset into an unbound text controls. The data set is
an address, but sometimes I will not have a street address or city.
For example, the data might look like this:

STREET CITY STATE
123 Main New York NY
345 Oak St NY
New York NY

There are two controls on the report, addressLine1 & addressLine2.
Using the above data, the addresses would be displayed like this:

addressLine1: 123 Main
addressLine2: New York, NY

addressLine1: 345 Oak St
addressLine2: NY

addressLine1: New York, NY
addressLine2:

I know I can do this if I bind controls to the street, city, and state
fields, hide them, then code addressLines 1 & 2 to display the data in
the format I want. However, I'd like to avoid putting the bound
controls on the report only to turn around and hide them.

If anyone has any suggestions, I would love to hear them.

Thanks & Ciao,

Tony

 

[Tony]

Doug,

Sounds good, I'll give it a go. Again, I really appreciate the assistance.

Ciao,

Tony

The formula I gave will give you exactly what you originally asked for.

If you have Street, City, State, you'll get:

Street
City, State

If you have Street, no City, State, you'll get:

Street
State

If you have no Street, City, State, you'll get:

City, State

I'm saying have a single text box for the address, not two. Make it one
line high, but set the Can Grow property to True.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

I need the second line in the event I have the address and state, but no
city. In that case, the address would go on line #1 and the state on
line # 2. Does that make sense(?) & again, thanks for the assistance.

Tony

Why do you need the 2nd line?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,

Thanks for the reply. I came across this approach in a KB article, but
the situation I'm trying to handle is a bit different. I'm working
with two address lines and if there is no street address, I want to
move the city & state information up to the first line and leave the
second blank.

Any suggestions under for that scenario?

Again, thanks for the help.

Tony

message If you create a single column in the underlying query, you could use
this in a single text box that's set to Grow.

([Street] + Chr(13) & Chr(10)) & ([City] + ", ") & [State]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi All,

I'm looking for a 'simple' way to concatenate fields from a report's
underlying recordset into an unbound text controls. The data set is
an address, but sometimes I will not have a street address or city.
For example, the data might look like this:

STREET CITY STATE
123 Main New York NY
345 Oak St NY
New York NY

There are two controls on the report, addressLine1 & addressLine2.
Using the above data, the addresses would be displayed like this:

addressLine1: 123 Main
addressLine2: New York, NY

addressLine1: 345 Oak St
addressLine2: NY

addressLine1: New York, NY
addressLine2:

I know I can do this if I bind controls to the street, city, and
state fields, hide them, then code addressLines 1 & 2 to display the
data in the format I want. However, I'd like to avoid putting the
bound controls on the report only to turn around and hide them.

If anyone has any suggestions, I would love to hear them.

Thanks & Ciao,

Tony