Adding carriage returns to a concatenated string in an update query

jurroppi1

New Member
Joined
Oct 21, 2002
Messages
28
I'm trying to get the update to field in an update query to keep the existing data and add a comment to it using an update query. the field I am appending to is called "RootCause".

the problem I am having is that I cannot figure out how to concatenate a CRLF between the existing string and the string I want to concatenate to the field. Currently I am able to get the existing string and another string concatentaed together without issue, but the carriage return linefeed item eludes me. Is there a way to do this without having to write a vbscript routine?

So far I've tried the following:
[RootCause] & Chr(10) & "7/18/2008 - TEST"
[RootCause] & Chr(13) & "7/18/2008 - TEST"
[RootCause] & Chr(10) + Chr(13) & "7/18/2008 - TEST"
[RootCause] & vbCrLf & "7/18/2008 - TEST"
(in the last example Access want to put quotes around the vbCrLf statement (I saw an example of this in another post entitled "Carriage Return Syntax to make address label".

thanks in advance for any help on this.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

huh?

Board Regular
Joined
Aug 3, 2004
Messages
72
try putting the following in the "Update To:" section for the field, [RootCause] in the Query Design grid view of your update query:
Code:
[RootCause] & Chr$(13) & Chr$(10) & "7/18/2008 - TEST"

Hope this helps.
 

jurroppi1

New Member
Joined
Oct 21, 2002
Messages
28
Works like a charm... Thank you so much for the help!

Where did you come up with the information to add the "$" modifier to the Chr function?

I swear that the help file for Access is pretty nebulous sometimes.
 

huh?

Board Regular
Joined
Aug 3, 2004
Messages
72
You're very welcome.
I googled it, using search criteria, ms access query carriage return line feed, or something similar and then tested it. :)
Both Chr and Chr$ will work. What matters is the order you put them in. In other words you have to put Chr(13), carriage return, and then Chr(10), line feed. Otherwise you get some strange characters added to the line. At least that is how it works in access 2003.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,709
Messages
5,626,411
Members
416,183
Latest member
IanA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top