appending more than 255 characters into a Memo field

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
Paul said:
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?
Click to expand...


Try removing the DISTINCT predicate and see what you get.
 
Paul said:
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?
Click to expand...


Try removing the DISTINCT predicate and see what you get.
 
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?
Click to expand...

Remove the DISTINCT for one thing - memos get truncated in a DISTINCT query.
 
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?
Click to expand...

Remove the DISTINCT for one thing - memos get truncated in a DISTINCT query.
 
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 said:
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?
Click to expand...

Remove the DISTINCT for one thing - memos get truncated in a DISTINCT
query.
Click to expand...
 
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 said:
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?
Click to expand...

Remove the DISTINCT for one thing - memos get truncated in a DISTINCT
query.
Click to expand...
 
Marsh, John - that did it. Thanks much.

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

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

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

will insert three lines.
 
Marsh, John - that did it. Thanks much.

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

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

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

will insert three lines.
 
Great!

Thanks, John



John W. Vinson said:
Marsh, John - that did it. Thanks much.

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

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

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

will insert three lines.
Click to expand...
 
Great!

Thanks, John



John W. Vinson said:
Marsh, John - that did it. Thanks much.

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

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

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

will insert three lines.
Click to expand...
 
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 said:
Marsh, John - that did it. Thanks much.

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

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

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

will insert three lines.
Click to expand...
 
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 said:
Marsh, John - that did it. Thanks much.

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

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

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

will insert three lines.
Click to expand...
 
Thanks for that clarification, Ken, because I will be using it in query
design view.


Ken Snell said:
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 said:
Marsh, John - that did it. Thanks much.

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

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

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

will insert three lines.
Click to expand...
Click to expand...
 
Back
Top