New Line in Query

[Tom Ventouris]

Is there a way to create a new line when merging two text fields in a query?
Something like vbCrLf

 

[Rob Parker]

Hi Tom,

Try this:
[Field1] & Chr(13) & Chr(10) & [Field2]

Rob

 

[Ken Snell \(MVP\)]

Chr(13) & Chr(10)

E.g.

SELECT Field1 & Chr(13) & Chr(10) & Field2 AS
TwoLineDataValue
FROM Tablename;

 

[Tom Ventouris]

Thank you.

Chr(13) & Chr(10)

E.g.

SELECT Field1 & Chr(13) & Chr(10) & Field2 AS
TwoLineDataValue
FROM Tablename;

 

[Tom Ventouris]

My apologies, but I was a bit too quick with this one. It's memos I am joining.
Your solution does work with my Text Boxes.

 

[Pieter Wijnen]

Look up AppendChunk in VBA Help

Pieter

My apologies, but I was a bit too quick with this one. It's memos I am
joining.
Your solution does work with my Text Boxes.

 

[Ken Snell \(MVP\)]

Post the full SQL statement of the query that you're trying to use.

 

[Tom Ventouris]

The frmComPanel is used to send info to all addresses in tblPRecords which
have not been archived. tblPRecords has a Memo called Pnotes. I am trying to
update by adding the contents of frmComPanel.NotesUpdate in a new line in
PNotes.
The Default value of NotesUpdate is "Info Sent: "& Date() but left visible
in case the user must enter any other note.

My SQL Statement:

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

This adds the note, but not in a new line for easy reading amongst all the
other notes that are recorded on individual records. If there is no other
way, I will change the statement to the following just to separate each
entry, or something like it. (Not my first choice)

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & "***"&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

Thanks for your time.

 

[Ken Snell \(MVP\)]

I assume that you have a typo in the SQL statement that you posted. It
should look like this:

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

The above SQL statement should add the new notes to a new line in the PNotes
field, and your comments suggest that this indeed is being done. Your
concern appears to be that you are not seeing this the way you want? Not
sure exactly what you want instead, but perhaps you might want to add a
blank line between the previous Notes and the newly added info?

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

--

Ken Snell
<MS ACCESS MVP>

The frmComPanel is used to send info to all addresses in tblPRecords which
have not been archived. tblPRecords has a Memo called Pnotes. I am trying
to
update by adding the contents of frmComPanel.NotesUpdate in a new line in
PNotes.
The Default value of NotesUpdate is "Info Sent: "& Date() but left visible
in case the user must enter any other note.

My SQL Statement:

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & Chr(13) & Chr(10)
&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

This adds the note, but not in a new line for easy reading amongst all the
other notes that are recorded on individual records. If there is no other
way, I will change the statement to the following just to separate each
entry, or something like it. (Not my first choice)

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & "***"&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

Thanks for your time.

Post the full SQL statement of the query that you're trying to use.

 

[Pieter Wijnen]

I think you have to go with my suggestion (AppendChunk)
The Example in help is quite good

Pieter

The frmComPanel is used to send info to all addresses in tblPRecords which
have not been archived. tblPRecords has a Memo called Pnotes. I am trying
to
update by adding the contents of frmComPanel.NotesUpdate in a new line in
PNotes.
The Default value of NotesUpdate is "Info Sent: "& Date() but left visible
in case the user must enter any other note.

My SQL Statement:

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & Chr(13) & Chr(10)
&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

This adds the note, but not in a new line for easy reading amongst all the
other notes that are recorded on individual records. If there is no other
way, I will change the statement to the following just to separate each
entry, or something like it. (Not my first choice)

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & "***"&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

Thanks for your time.

Post the full SQL statement of the query that you're trying to use.

 

[Tom Ventouris]

Thank you
I am looking through the help and will post back.

I think you have to go with my suggestion (AppendChunk)
The Example in help is quite good

Pieter

The frmComPanel is used to send info to all addresses in tblPRecords which
have not been archived. tblPRecords has a Memo called Pnotes. I am trying
to
update by adding the contents of frmComPanel.NotesUpdate in a new line in
PNotes.
The Default value of NotesUpdate is "Info Sent: "& Date() but left visible
in case the user must enter any other note.

My SQL Statement:

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & Chr(13) & Chr(10)
&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

This adds the note, but not in a new line for easy reading amongst all the
other notes that are recorded on individual records. If there is no other
way, I will change the statement to the following just to separate each
entry, or something like it. (Not my first choice)

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & "***"&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

Thanks for your time.

Post the full SQL statement of the query that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>


My apologies, but I was a bit too quick with this one. It's memos I am
joining.
Your solution does work with my Text Boxes.

:

Chr(13) & Chr(10)

E.g.

SELECT Field1 & Chr(13) & Chr(10) & Field2 AS
TwoLineDataValue
FROM Tablename;

--

Ken Snell
<MS ACCESS MVP>


message
Is there a way to create a new line when merging two text fields in
a
query?
Something like vbCrLf

 

[Ken Snell \(MVP\)]

How are you viewing the field? In a query? In a report? In a form? In an
EXCEl worksheet?

To be sure I understand, you say the new note info is being added to the end
of the existing string in PNotes field, but that you're not seeing it start
on a new line?

--

Ken Snell
<MS ACCESS MVP>

Thank you for the quick response. I did have a typo.
I DO want the new note in a new line, and you are correct - this is not
happening. The new note is added at the end of the existing note.

I have copied your statement, no change. I have tried changing the Enter
Key
Behaviour of PNotes - no change.

I assume that you have a typo in the SQL statement that you posted. It
should look like this:

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

The above SQL statement should add the new notes to a new line in the
PNotes
field, and your comments suggest that this indeed is being done. Your
concern appears to be that you are not seeing this the way you want? Not
sure exactly what you want instead, but perhaps you might want to add a
blank line between the previous Notes and the newly added info?

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

 

[Tom Ventouris]

PNotes is a Memo on a MyForm1
NotesUpdate is a TextBox on MyForm2

My SQL Adds the contents of NotesUpdate to PNotes - this is added to the end
of the existing string.

I want it to add to a new line. I am viewing PNotes in a MyForm1

I am looking for:

PNotes= [PNotes] & New Line & [NotesUpdate]

I have tried: (Extracts from the SQL)
[PNotes] & Chr(13) & Chr(10) & [NotesUpdate]

Also Tried:
[PNotes] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NotesUpdate]

I get:
[PNotes][NewNotes]

I am expecting:
[PNotes]
[NewNotes]

Thanks for all your trouble on this.

How are you viewing the field? In a query? In a report? In a form? In an
EXCEl worksheet?

To be sure I understand, you say the new note info is being added to the end
of the existing string in PNotes field, but that you're not seeing it start
on a new line?

--

Ken Snell
<MS ACCESS MVP>

Thank you for the quick response. I did have a typo.
I DO want the new note in a new line, and you are correct - this is not
happening. The new note is added at the end of the existing note.

I have copied your statement, no change. I have tried changing the Enter
Key
Behaviour of PNotes - no change.

I assume that you have a typo in the SQL statement that you posted. It
should look like this:

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

The above SQL statement should add the new notes to a new line in the
PNotes
field, and your comments suggest that this indeed is being done. Your
concern appears to be that you are not seeing this the way you want? Not
sure exactly what you want instead, but perhaps you might want to add a
blank line between the previous Notes and the newly added info?

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

 

[Tom Ventouris]

PS: In anser to your questions:

How are you viewing the field? In a query? In a report? In a form? In an
EXCEl worksheet? Answer: Form Memo Field

To be sure I understand, you say the new note info is being added to the end
of the existing string in PNotes field, but that you're not seeing it start
on a new line? Answer: Yes, and it's killing me!

--

Ken Snell
<MS ACCESS MVP>

Thank you for the quick response. I did have a typo.
I DO want the new note in a new line, and you are correct - this is not
happening. The new note is added at the end of the existing note.

I have copied your statement, no change. I have tried changing the Enter
Key
Behaviour of PNotes - no change.

I assume that you have a typo in the SQL statement that you posted. It
should look like this:

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

The above SQL statement should add the new notes to a new line in the
PNotes
field, and your comments suggest that this indeed is being done. Your
concern appears to be that you are not seeing this the way you want? Not
sure exactly what you want instead, but perhaps you might want to add a
blank line between the previous Notes and the newly added info?

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

 

[Ken Snell \(MVP\)]

What are the "Height" and "Width" settings of the TextBox? If the textbox is
just "one line" tall, make it taller; that will cause ACCESS to wordwrap,
which then should show the new line for the field's data. Be sure that you
set ScrollBars to Vertical for the textbox too.

--

Ken Snell
<MS ACCESS MVP>

PNotes is a Memo on a MyForm1
NotesUpdate is a TextBox on MyForm2

My SQL Adds the contents of NotesUpdate to PNotes - this is added to the
end
of the existing string.

I want it to add to a new line. I am viewing PNotes in a MyForm1

I am looking for:

PNotes= [PNotes] & New Line & [NotesUpdate]

I have tried: (Extracts from the SQL)
[PNotes] & Chr(13) & Chr(10) & [NotesUpdate]

Also Tried:
[PNotes] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NotesUpdate]

I get:
[PNotes][NewNotes]

I am expecting:
[PNotes]
[NewNotes]

Thanks for all your trouble on this.

How are you viewing the field? In a query? In a report? In a form? In an
EXCEl worksheet?

To be sure I understand, you say the new note info is being added to the
end
of the existing string in PNotes field, but that you're not seeing it
start
on a new line?

--

Ken Snell
<MS ACCESS MVP>

Thank you for the quick response. I did have a typo.
I DO want the new note in a new line, and you are correct - this is not
happening. The new note is added at the end of the existing note.

I have copied your statement, no change. I have tried changing the
Enter
Key
Behaviour of PNotes - no change.

:

I assume that you have a typo in the SQL statement that you posted. It
should look like this:

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

The above SQL statement should add the new notes to a new line in the
PNotes
field, and your comments suggest that this indeed is being done. Your
concern appears to be that you are not seeing this the way you want?
Not
sure exactly what you want instead, but perhaps you might want to add
a
blank line between the previous Notes and the newly added info?

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

 

[Tom Ventouris]

Thank you for sticking with my problem here. The Height and ScrollBars of the
TextBox did not make a diffrence. The problem was RichText.
I put the PNotes Memo back to Plain Text and........if you could see me now!

The SQLbelow, which you suggested earlier works!

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

Thank you again.

What are the "Height" and "Width" settings of the TextBox? If the textbox is
just "one line" tall, make it taller; that will cause ACCESS to wordwrap,
which then should show the new line for the field's data. Be sure that you
set ScrollBars to Vertical for the textbox too.

--

Ken Snell
<MS ACCESS MVP>

PNotes is a Memo on a MyForm1
NotesUpdate is a TextBox on MyForm2

My SQL Adds the contents of NotesUpdate to PNotes - this is added to the
end
of the existing string.

I want it to add to a new line. I am viewing PNotes in a MyForm1

I am looking for:

PNotes= [PNotes] & New Line & [NotesUpdate]

I have tried: (Extracts from the SQL)
[PNotes] & Chr(13) & Chr(10) & [NotesUpdate]

Also Tried:
[PNotes] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NotesUpdate]

I get:
[PNotes][NewNotes]

I am expecting:
[PNotes]
[NewNotes]

Thanks for all your trouble on this.

How are you viewing the field? In a query? In a report? In a form? In an
EXCEl worksheet?

To be sure I understand, you say the new note info is being added to the
end
of the existing string in PNotes field, but that you're not seeing it
start
on a new line?

--

Ken Snell
<MS ACCESS MVP>


Thank you for the quick response. I did have a typo.
I DO want the new note in a new line, and you are correct - this is not
happening. The new note is added at the end of the existing note.

I have copied your statement, no change. I have tried changing the
Enter
Key
Behaviour of PNotes - no change.

:

I assume that you have a typo in the SQL statement that you posted. It
should look like this:

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

The above SQL statement should add the new notes to a new line in the
PNotes
field, and your comments suggest that this indeed is being done. Your
concern appears to be that you are not seeing this the way you want?
Not
sure exactly what you want instead, but perhaps you might want to add
a
blank line between the previous Notes and the newly added info?

UPDATE tblPRecords SET tblPRecords.[PNotes] =
tblPRecords.[PNotes] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) &
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

 

[Ken Snell \(MVP\)]

Thank you for sticking with my problem here. The Height and ScrollBars of
the
TextBox did not make a diffrence. The problem was RichText.

Thanks for the additional post about how you fixed it. I hadn't considered
this possibility; so I'll be putting this in my "archive" for the future.
Thanks.

 

[Pieter Wijnen]

On a general note - if the notes added are all in the 255 char limit, I'd
rather use a text field in a seperate table and combine using a text or
list -box to combine

Pieter

Thank you
I am looking through the help and will post back.

I think you have to go with my suggestion (AppendChunk)
The Example in help is quite good

Pieter

The frmComPanel is used to send info to all addresses in tblPRecords
which
have not been archived. tblPRecords has a Memo called Pnotes. I am
trying
to
update by adding the contents of frmComPanel.NotesUpdate in a new line
in
PNotes.
The Default value of NotesUpdate is "Info Sent: "& Date() but left
visible
in case the user must enter any other note.

My SQL Statement:

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & Chr(13) &
Chr(10)
&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

This adds the note, but not in a new line for easy reading amongst all
the
other notes that are recorded on individual records. If there is no
other
way, I will change the statement to the following just to separate each
entry, or something like it. (Not my first choice)

UPDATE tblPRecords SET tblPRecords.PNotes] = [PNotes] & "***"&
[Forms]![frmComPanel]![NotesUpdate]
WHERE (((tblPRecords.Archive)=False));

Thanks for your time.




:

Post the full SQL statement of the query that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>


message
My apologies, but I was a bit too quick with this one. It's memos I
am
joining.
Your solution does work with my Text Boxes.

:

Chr(13) & Chr(10)

E.g.

SELECT Field1 & Chr(13) & Chr(10) & Field2 AS
TwoLineDataValue
FROM Tablename;

--

Ken Snell
<MS ACCESS MVP>


message
Is there a way to create a new line when merging two text fields
in
a
query?
Something like vbCrLf