If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
appending more than 255 characters into a Memo field
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] |
#3
|
|||
|
|||
appending more than 255 characters into a Memo field
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] |
#4
|
|||
|
|||
appending more than 255 characters into a Memo field
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] |
#5
|
|||
|
|||
appending more than 255 characters into a Memo field
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] |
#6
|
|||
|
|||
appending more than 255 characters into a Memo field
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] |
#7
|
|||
|
|||
appending more than 255 characters into a Memo field
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] |
#8
|
|||
|
|||
appending more than 255 characters into a Memo field
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] |
Thread Tools | |
Display Modes | |
|
|