New Line in Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom Ventouris - 15 Oct 2007 14:08 GMT

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

Reply to this Message

Rob Parker - 15 Oct 2007 14:23 GMT

Hi Tom,

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

Rob

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

Reply to this Message

Ken Snell (MVP) - 15 Oct 2007 14:27 GMT

Chr(13) & Chr(10)

E.g.

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

Signature

Ken Snell
<MS ACCESS MVP>

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

Reply to this Message

Tom Ventouris - 15 Oct 2007 14:51 GMT

Thank you.

> Chr(13) & Chr(10)
>
[quoted text clipped - 7 lines]
> > query?
> > Something like vbCrLf

Reply to this Message

Tom Ventouris - 15 Oct 2007 15:04 GMT

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)
>
[quoted text clipped - 7 lines]
> > query?
> > Something like vbCrLf

Reply to this Message

Pieter Wijnen - 15 Oct 2007 15:56 GMT

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.
[quoted text clipped - 11 lines]
>> > query?
>> > Something like vbCrLf

Reply to this Message

Ken Snell (MVP) - 15 Oct 2007 16:55 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> My apologies, but I was a bit too quick with this one. It's memos I am
> joining.
[quoted text clipped - 11 lines]
>> > query?
>> > Something like vbCrLf

Reply to this Message

Tom Ventouris - 15 Oct 2007 17:52 GMT

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.
>
[quoted text clipped - 13 lines]
> >> > query?
> >> > Something like vbCrLf

Reply to this Message

Ken Snell (MVP) - 15 Oct 2007 17:58 GMT

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));

Signature

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
[quoted text clipped - 40 lines]
>> >> > query?
>> >> > Something like vbCrLf

Reply to this Message

Tom Ventouris - 15 Oct 2007 18:31 GMT

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:
[quoted text clipped - 60 lines]
> >> >> > query?
> >> >> > Something like vbCrLf

Reply to this Message

Ken Snell (MVP) - 15 Oct 2007 19:21 GMT

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?

Signature

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
[quoted text clipped - 24 lines]
>> [Forms]![frmComPanel]![NotesUpdate]
>> WHERE (((tblPRecords.Archive)=False));

Reply to this Message

Tom Ventouris - 15 Oct 2007 19:40 GMT

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?
[quoted text clipped - 31 lines]
> >> [Forms]![frmComPanel]![NotesUpdate]
> >> WHERE (((tblPRecords.Archive)=False));

Reply to this Message

Ken Snell (MVP) - 15 Oct 2007 19:55 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> PNotes is a Memo on a MyForm1
> NotesUpdate is a TextBox on MyForm2
[quoted text clipped - 64 lines]
>> >> [Forms]![frmComPanel]![NotesUpdate]
>> >> WHERE (((tblPRecords.Archive)=False));

Reply to this Message

Tom Ventouris - 15 Oct 2007 20:36 GMT

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,
[quoted text clipped - 69 lines]
> >> >> [Forms]![frmComPanel]![NotesUpdate]
> >> >> WHERE (((tblPRecords.Archive)=False));

Reply to this Message

Ken Snell (MVP) - 15 Oct 2007 20:43 GMT

> 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.

Signature

Ken Snell
<MS ACCESS MVP>

Reply to this Message

Tom Ventouris - 15 Oct 2007 19:46 GMT

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
[quoted text clipped - 31 lines]
> >> [Forms]![frmComPanel]![NotesUpdate]
> >> WHERE (((tblPRecords.Archive)=False));

Reply to this Message

Pieter Wijnen - 15 Oct 2007 18:07 GMT

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

Pieter

Reply to this Message

Tom Ventouris - 15 Oct 2007 18:31 GMT

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
[quoted text clipped - 45 lines]
> >> >> > query?
> >> >> > Something like vbCrLf

Reply to this Message

Pieter Wijnen - 15 Oct 2007 20:43 GMT

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.
[quoted text clipped - 57 lines]
>> >> >> > query?
>> >> >> > Something like vbCrLf

Reply to this Message