appending more than 255 characters into a Memo field

[Paul]

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

 

[Marshall Barton]

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.

 

[Marshall Barton]

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.

 

[John W. Vinson]

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]

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.

 

[Paul]

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

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.

 

[Paul]

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

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]

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]

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.

 

[Paul]

Great!

Thanks, John

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.

 

[Paul]

Great!

Thanks, John

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.

 

[Ken Snell [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/

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.

 

[Ken Snell [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/

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.

 

[Paul]

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

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/

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.