appending more than 255 characters into a Memo field

I'm trying to run an append query that that concatenates data from one Text
field and two Memo fields into a single Memo field in the destination table.
However, I have noticed that it will only append a maximum of 255
characters, and truncates the rest. The field in the destination table does
have a data type of Memo.

Here is the SQL code I'm using:

INSERT INTO tbl_update_Agency_Notes ( ProjectID, Agency, AdditionalNotes,
Unit )
SELECT DISTINCT Val([ProjNum]) AS ProjectID, tblProjEntry.Agency,
IIf(Left([Scope],8)=Left([RESDScope],8),[ProjTitle] & " - " &
[RESDScope],[ProjTitle] & " - " & [Scope] & " - " & [RESDScope]) AS
AdditionalNotes,
IIf(Len(nz([UnitDescription]))=0,[AgencyUnit],[UnitDescription]) AS Unit
FROM tblProjEntry
WHERE ((Len(nz(Val([ProjNum]))))="6");

Is there anything I can do to prevent this truncation so that all of the
concatenated data will be appended to the destination table?

Thanks in advance.

Paul

Paul wrote:

I'm trying to run an append query that that concatenates data from one Text
field and two Memo fields into a single Memo field in the destination table.
However, I have noticed that it will only append a maximum of 255
characters, and truncates the rest. The field in the destination table does
have a data type of Memo.

Here is the SQL code I'm using:

INSERT INTO tbl_update_Agency_Notes ( ProjectID, Agency, AdditionalNotes,
Unit )
SELECT DISTINCT Val([ProjNum]) AS ProjectID, tblProjEntry.Agency,
IIf(Left([Scope],8)=Left([RESDScope],8),[ProjTitle] & " - " &
[RESDScope],[ProjTitle] & " - " & [Scope] & " - " & [RESDScope]) AS
AdditionalNotes,
IIf(Len(nz([UnitDescription]))=0,[AgencyUnit],[UnitDescription]) AS Unit
FROM tblProjEntry
WHERE ((Len(nz(Val([ProjNum]))))="6");

Is there anything I can do to prevent this truncation so that all of the
concatenated data will be appended to the destination table?


Try removing the DISTINCT predicate and see what you get.

--
Marsh
MVP [MS Access]

On Sat, 30 May 2009 15:30:27 -0700, "Paul" wrote:

I'm trying to run an append query that that concatenates data from one Text
field and two Memo fields into a single Memo field in the destination table.
However, I have noticed that it will only append a maximum of 255
characters, and truncates the rest. The field in the destination table does
have a data type of Memo.

Here is the SQL code I'm using:

INSERT INTO tbl_update_Agency_Notes ( ProjectID, Agency, AdditionalNotes,
Unit )
SELECT DISTINCT Val([ProjNum]) AS ProjectID, tblProjEntry.Agency,
IIf(Left([Scope],8)=Left([RESDScope],8),[ProjTitle] & " - " &
[RESDScope],[ProjTitle] & " - " & [Scope] & " - " & [RESDScope]) AS
AdditionalNotes,
IIf(Len(nz([UnitDescription]))=0,[AgencyUnit],[UnitDescription]) AS Unit
FROM tblProjEntry
WHERE ((Len(nz(Val([ProjNum]))))="6");

Is there anything I can do to prevent this truncation so that all of the
concatenated data will be appended to the destination table?

Remove the DISTINCT for one thing - memos get truncated in a DISTINCT query.
--

John W. Vinson [MVP]

Marsh, John - that did it. Thanks much.

One last question - is there a way to insert a carriage return between the
strings being concatenated?

Paul




"John W. Vinson" wrote in message
...
On Sat, 30 May 2009 15:30:27 -0700, "Paul" wrote:

I'm trying to run an append query that that concatenates data from one
Text
field and two Memo fields into a single Memo field in the destination
table.
However, I have noticed that it will only append a maximum of 255
characters, and truncates the rest. The field in the destination table
does
have a data type of Memo.

Here is the SQL code I'm using:

INSERT INTO tbl_update_Agency_Notes ( ProjectID, Agency, AdditionalNotes,
Unit )
SELECT DISTINCT Val([ProjNum]) AS ProjectID, tblProjEntry.Agency,
IIf(Left([Scope],8)=Left([RESDScope],8),[ProjTitle] & " - " &
[RESDScope],[ProjTitle] & " - " & [Scope] & " - " & [RESDScope]) AS
AdditionalNotes,
IIf(Len(nz([UnitDescription]))=0,[AgencyUnit],[UnitDescription]) AS Unit
FROM tblProjEntry
WHERE ((Len(nz(Val([ProjNum]))))="6");

Is there anything I can do to prevent this truncation so that all of the
concatenated data will be appended to the destination table?

Remove the DISTINCT for one thing - memos get truncated in a DISTINCT
query.
--

John W. Vinson [MVP]

On Sat, 30 May 2009 17:01:41 -0700, "Paul" wrote:

Marsh, John - that did it. Thanks much.

One last question - is there a way to insert a carriage return between the
strings being concatenated?


Sure. Just concatenate the VBA string constant vbCrLf, or (equivalently)
Chr(13) & Chr(10):

[ProjTitle] & vbCrLf & [Scope] & vbCrLf & [RESDScope]

will insert three lines.

--

John W. Vinson [MVP]

Great!

Thanks, John



"John W. Vinson" wrote in message
...
On Sat, 30 May 2009 17:01:41 -0700, "Paul" wrote:

Marsh, John - that did it. Thanks much.

One last question - is there a way to insert a carriage return between the
strings being concatenated?


Sure. Just concatenate the VBA string constant vbCrLf, or (equivalently)
Chr(13) & Chr(10):

[ProjTitle] & vbCrLf & [Scope] & vbCrLf & [RESDScope]

will insert three lines.

--

John W. Vinson [MVP]

Only in VBA code, though, can you use vbCrLf.

Otherwise, for a query in design view, replace
vbCrLf

with
Chr(13) & Chr(10)
--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"John W. Vinson" wrote in message
...
On Sat, 30 May 2009 17:01:41 -0700, "Paul" wrote:

Marsh, John - that did it. Thanks much.

One last question - is there a way to insert a carriage return between the
strings being concatenated?


Sure. Just concatenate the VBA string constant vbCrLf, or (equivalently)
Chr(13) & Chr(10):

[ProjTitle] & vbCrLf & [Scope] & vbCrLf & [RESDScope]

will insert three lines.

--

John W. Vinson [MVP]

Thanks for that clarification, Ken, because I will be using it in query
design view.


"Ken Snell [MVP]" wrote in message
...
Only in VBA code, though, can you use vbCrLf.

Otherwise, for a query in design view, replace
vbCrLf

with
Chr(13) & Chr(10)
--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"John W. Vinson" wrote in message
...
On Sat, 30 May 2009 17:01:41 -0700, "Paul"
wrote:

Marsh, John - that did it. Thanks much.

One last question - is there a way to insert a carriage return between
the
strings being concatenated?


Sure. Just concatenate the VBA string constant vbCrLf, or (equivalently)
Chr(13) & Chr(10):

[ProjTitle] & vbCrLf & [Scope] & vbCrLf & [RESDScope]

will insert three lines.

--

John W. Vinson [MVP]