Multiple values into one cell with each on a new line

[Guest]

I am trying to concatenate [Street 1], [Street 2], City, State, and Zip
fields into one field called Address in a separate table. Is it possible to
create a query that would use INSERT INTO and add the next field as a new
line below the previous one? I tried using VB but all I got was the little
white and not a new line. I used vbKeyReturn. Please any help would be
greatly appreciated. Thanks

 

[Jeff Boyce]

Access is a relational database. What you described sounds like how you
would have to handle your situation if you were using ... a spreadsheet!
Data specific to one row stays on that row, not in a new row.

By the way, if you already have Street/C/S/Z, why bother concatenating them
and (redundantly) storing them? You can generate the concantenated string
whenever you need it by using a query (and you don't run the risk of your
calculated field ending up out of synch with the original data).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chris2]

I am trying to concatenate [Street 1], [Street 2], City, State, and Zip
fields into one field called Address in a separate table.

I understand.

Is it possible to
create a query that would use INSERT INTO and add the next field as a new
line below the previous one?

From the way I am reading this, you seem to want to have a line-break
appear at various points in your final string, separating the
information from the original columns, so that the mailing address is
properly formatted when the data in the column is printed in your
printing system (whatever it is).

When you are concatenating the original columns into one column, just
insert an extra Visal Basic code step to add a line-break character
that will be recognized properly by whatever printing system you
happen to be using.

If you want MS Access itself to display information on multiple lines
in a cell on a datasheet, I'm am thinking that is not possible.

I tried using VB but all I got was the little
white and not a new line. I used vbKeyReturn.

MS Access' datasheet is not meant to do more than show information in
its most basic form. The datasheet does not support formatting of any
kind, and cannot (as far as I know) recognize any type of formatting
information that might be stored in any column of any row. (I'm on MS
Access 2000, later versions may have changed that.)

Thanks

You're welcome.


Sincerely,

Chris O.

 

[Jamie Collins]

Access is a relational database.

Doubtful With all the will in the world Access/Jet just about makes
the mark as a SQL DBMS.

if you already have Street/C/S/Z, why bother concatenating them
and (redundantly) storing them? You can generate the concantenated string
whenever you need it by using a query

In a SQL DBMS resultset, this would constitute violating First Normal
Form. In a relational database resultset, this would be unthinkable
<g>!

Jamie.

--