Query output limiting field length - Access 2007

[Guest]

Below is Access SQL for review...
SELECT PublishingDetailsHistory.AgreementID,
PublishingDetailsHistory.SubjectMatter
FROM PublishingDetailsHistory
WHERE (((PublishingDetailsHistory.AgreementID)=1784));

The actual field length in the table for one of my records is 748 but the
query ouput is only showing 337. The next word in the string which is not
coming back is "layout,". Any advice on how to make the entire 748
characters show in query output?

 

[Allen Browne]

That should work, assuming PublishingDetailsHistory is a Table (not another
query where more could be going on), and SubjectMatter is a Memo field (not
the result of a function or concatenation where other isuses could apply.)

Is there any chance there is a carriage return/line feed at that place in
the field, so that the rest of the characters don't show? To test the idea,
place the cursor in the field in that record, and press Shift+F2 to open the
zoom box.

It is possible that there is another character (such as the null char, i.e.
Chr(0)) which is preventing the remaining characters from displaying. You
can Access Access to show you that with:
Asc(Mid([SubjectMatter], 338,1))

 

[Guest]

Thank you for the response...it looks like I have a carriage return within
the field. Next question is how do I get around this? Is there a way I can
modify the datatable to remove those? Or use some kind of function over the
field within the query to ignore it? Thank you.

That should work, assuming PublishingDetailsHistory is a Table (not another
query where more could be going on), and SubjectMatter is a Memo field (not
the result of a function or concatenation where other isuses could apply.)

Is there any chance there is a carriage return/line feed at that place in
the field, so that the rest of the characters don't show? To test the idea,
place the cursor in the field in that record, and press Shift+F2 to open the
zoom box.

It is possible that there is another character (such as the null char, i.e.
Chr(0)) which is preventing the remaining characters from displaying. You
can Access Access to show you that with:
Asc(Mid([SubjectMatter], 338,1))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Below is Access SQL for review...
SELECT PublishingDetailsHistory.AgreementID,
PublishingDetailsHistory.SubjectMatter
FROM PublishingDetailsHistory
WHERE (((PublishingDetailsHistory.AgreementID)=1784));

The actual field length in the table for one of my records is 748 but the
query ouput is only showing 337. The next word in the string which is not
coming back is "layout,". Any advice on how to make the entire 748
characters show in query output?

 

[Guest]

I spoke too soon. Asc(Mid([SubjectMatter], 338,1)) returns the code 108 for
a lower case L. The whole word is "layout" which I don't believe is a
"reserved" word or a command. Any other reason why the data would stop
returning at the lower case L...337 character position?

Ah, just found it...the query result box is limiting the visual output to
337 characters for one line...the remaining data is in the query output
however it is like it is word wrapped at character 337. In my data, 748
characters, there are 3 lines of text within the query output. I simply did
not know to look under the first line of text...I stumbled upon this as I was
looking at the data within the table view as the same behaviour was in the
table view.

That should work, assuming PublishingDetailsHistory is a Table (not another
query where more could be going on), and SubjectMatter is a Memo field (not
the result of a function or concatenation where other isuses could apply.)

Is there any chance there is a carriage return/line feed at that place in
the field, so that the rest of the characters don't show? To test the idea,
place the cursor in the field in that record, and press Shift+F2 to open the
zoom box.

It is possible that there is another character (such as the null char, i.e.
Chr(0)) which is preventing the remaining characters from displaying. You
can Access Access to show you that with:
Asc(Mid([SubjectMatter], 338,1))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Below is Access SQL for review...
SELECT PublishingDetailsHistory.AgreementID,
PublishingDetailsHistory.SubjectMatter
FROM PublishingDetailsHistory
WHERE (((PublishingDetailsHistory.AgreementID)=1784));

The actual field length in the table for one of my records is 748 but the
query ouput is only showing 337. The next word in the string which is not
coming back is "layout,". Any advice on how to make the entire 748
characters show in query output?