Output to Memo field truncated

[trevorwilliams2]

I have a query that appends a record to a notes table.
The notes field in this table is set to Memo.
The output to the memo field, from the query, is limited to 255 characters with the last few characters being Japanese / Kanji.
The source data appears fine.

I have tried the following suggestions:
Made sure “group by“ is not being used in my SQL.
In query properties unique values is set to no.

I haven’t been able to figure this one out yet.
Any suggestions would be much appreciated.

Access 2003

My SQL:

INSERT INTO NOTES ( NoteProjCode, NoteConID, NoteAuthor, NoteType, NoteIssueID, NoteDate, NoteMemo, NoteHyperlink, DateUpdated, UpdatedBy )
SELECT DIRECTORY.DirProjCode, DIRECTORY.DirConID, [Forms]![PROJECTS]![PROJECTS_DIRECTORY]![DirConID] AS Author, "DIRECTORY EMAIL" AS [Note Type], [Forms]![frmDirectoryContactTransactions]![TextTransactionIssue] AS Issue, Date() AS [Note Date], [Forms]![frmDirectoryContactTransactions]![TextMessageBody] AS [Note], [Forms]![frmDirectoryContactTransactions]![TextEmailLocation] AS [Note Link], Now() AS Updated, fOSUserName() AS [Updated By]
FROM DIRECTORY
WHERE (((DIRECTORY.DirProjCode)=[Forms]![PROJECTS]![ProjCode]) AND ((DIRECTORY.DirConID)=[Forms]![PROJECTS]![PROJECTS_DIRECTORY]![DirConID]));

 

[MajP]

This covers a lot of the reasons for truncation.

http://allenbrowne.com/ser-63.html

And there are quite a few reasons.

However, I would think that this would work. I do not see one of the causes. Only issue could be a format property on the text box.

 

[trevorwilliams2]

Hi MajP, thanks for your reply. I usually end up at Allen's site one way or the other when trying to figure something out.

I went through his listings but no Dice.

The data in question is actually being extracted from the body of an Outlook email that currently has the focus.

Set myItem = ActiveExplorer.Selection.item(1)
Forms!frmDirectoryContactTransactions!TextMessageBody = myItem.Body

Not sure if this would make a difference?

?myItem.Body in the debug window returns the full text.

 

[MajP]

If you just do a select query instead of the insert does it still truncate? You do not have any of the usual suspects: aggregate query, distinct predicate, fomatted memo.

Does you table have a primary key?

 

[trevorwilliams2]

Hi MajP, thanks for you reply.

1. Still truncates as a select query. (sent all c?????)
2. No aggregate in the query.
3. No Distinct
4. Formatted memo: Not sure I understand this.
5. Primary Key: I can’t believe that I didn’t have one. (assumed I did) Added auto number set as primary, but still the same results.

 

[boblarson]

My suggestion (which has worked in other situations):

1. Create a query with all of the fields you want inserted EXCEPT the memo field. Put the criteria on that.

2. In a NEW query use that first query, linked by primary key and include all of the applicable fields from the first query and then the memo field from the second.

Having criteria in the query with the memo field in there seems to choke. So, if you do up the first query which pulls everything based on the criteria and then add in the memo by linking it to the other query, it seems to be able to work where it might not normally do so.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[dhookom]

Good idea Bob. Great to see you back in the Access saddle.

Duane
Hook'D on Access
MS Access MVP

 

[trevorwilliams2]

Bob, that did the trick! I was at the point of designin around it. Thanks!

 

[boblarson]

Glad it worked out for you.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com