Access 2013 add a line break or a carriage return in an expression

Using Access 2013 Web App. (very successfully.)

Designing queries that dump to Excel for reporting.

There are some name and some address fields being concatenated.  Need a line break or carriage return to format the result in Excel on multi-line.
VirtualKansasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
As you concatenate, try doing this

Select Name, Address1 & vbCRLF & Address2 & vbCRLF & Address3 from yourtable

Kelvin
0
VirtualKansasAuthor Commented:
Just a note; this Access 2013 Web App; doesn't like anything about the syntax of your suggestion?
0
VirtualKansasAuthor Commented:
Concat([FirstName],& vbCRLF,[LastName])

Comlaint is & is invalid text
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kelvin SparksCommented:
OK, I didn't relise it was the Web App. I personally don't use them as I don't like data macros.

What I suggested was VBA for Access desktop.


Kelvin
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Personally I think you're better off dumping the raw data into Excel and doing your formatting there.

Comlaint is & is invalid text
Not sure what you mean here, but if you're saying the ampersand is invalid, try using the + sign.
0
VirtualKansasAuthor Commented:
Invalid field name vbCRLF when using + instead of &
0
VirtualKansasAuthor Commented:
So, found a suggested workaround; create a utility table in access web app, with a long text field.  Then past a carriage return in the field.  Then reference that field in the formula.  Cool idea, but Excel is just returning the new field as text.
0
VirtualKansasAuthor Commented:
And to your credit Scott, if I compound the &CHAR(10)& in an Excel concatenation formula (do the formatting in Excel) and word wrap, does achieve the desired effect.  Just a sadness since many existing reports could have benefited if I could embed the carriage return into the Access query.
0
Nick67Commented:
WebApp & vbCRLF are likely not going to play given the webapps are VBA-free
https://social.msdn.microsoft.com/Forums/office/en-US/97abaf0c-eda8-4060-b892-2fd0f3ccd5b3/linefeed-character-in-access-2010-web-application-published-on-sharepoint?forum=accessdev

There are a number of just gross workarounds, but for your purposes, probably the best is to put vbCrLf into a dummy table's field and then JOIN the dummy table in and concatenate the vbcrlf field in where you need it.
0
VirtualKansasAuthor Commented:
Is there a way to embed the carriage return (line feed, whatever needed) in the new table long text field that Excel will see as not just another text entry?
0
Nick67Commented:
It looks like Excel wants only vbLf or CHAR(10), and the cell must have 'wrap text' properties set in order for that to work.
No wrap text, no joy.

Note that when you are creating the dummy table, you are not putting "CHAR(10)" in there.  That's a string of 8 characters.
You are looking to get a line feed in there.
That's hard to do!

I got one in via an update query by updating the dummy field to Chr(10)
You may need to do that from desktop Access.

I can get it to happen, now in Desktop Access.
I created table1 for the dummy field, and used an update query to get Chr(10) in there.  I threw in a couple text records and a field to concatenate them

When I punch the whole table out via TransferSpreadsheet, it doesn't look good -- until I turn on wrap text for TheConcat, and then it is good

Macros are no fun, but here is VBA that works strictly with fields to get it done

Dim rs As Recordset
Dim theValue As String
Set rs = CurrentDb.OpenRecordset("select * from table1", dbOpenDynaset)
Me.Text2.Value = ""
With rs
    .MoveFirst
    theValue = !thetext
    .MoveNext

    Do Until rs.EOF
        Me.Text2.Value = Me.Text2.Value & !thetext & theValue
        .Edit
        !theconcat = Me.Text2.Value
        .Update
        rs.MoveNext
    Loop
End With
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table1", "c:\temppdf\temp.xls", True

Open in new window


You should be able to get something similar to work with macros.
The key is actually getting that Chr(10) into a field.
Then paste a carriage return in the field.
NOPE!
Access in copy-paste translates them to vbcrlf, and that isn't what you want for Excel!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VirtualKansasAuthor Commented:
Crud,

Feels like I'm missing a step
a) create new table in desktop access -> dummy
b) run update query for Char10 to new field in dummy
c) import table into access web app
d) concatenate new field and no joy
0
Nick67Commented:
Not Char(10) -- that's Excel syntax
Chr(10)
When you get it right, the field will appear blank in Access.
Explore concatenating with it on the desktop Access and transferspreadsheet to Excel.
Once you get THAT working, then kick the table to your web app
0
VirtualKansasAuthor Commented:
OK, that worked on desktop!!!
0
VirtualKansasAuthor Commented:
Appreciate follow through and hand holding...
0
Nick67Commented:
Not a problem.
Glad you got it working
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.