A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query output limiting field length - Access 2007



 
 
Thread Tools Display Modes
  #1  
Old February 20th, 2007, 03:19 AM posted to microsoft.public.access.queries
ACCESS2007
external usenet poster
 
Posts: 6
Default Query output limiting field length - Access 2007

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?
  #2  
Old February 20th, 2007, 05:31 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default Query output limiting field length - Access 2007

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Access2007" wrote in message
...
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?


  #3  
Old February 21st, 2007, 02:38 AM posted to microsoft.public.access.queries
ACCESS2007
external usenet poster
 
Posts: 6
Default Query output limiting field length - Access 2007

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.

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Access2007" wrote in message
...
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?



  #4  
Old February 21st, 2007, 03:05 AM posted to microsoft.public.access.queries
ACCESS2007
external usenet poster
 
Posts: 6
Default Query output limiting field length - Access 2007

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.



"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Access2007" wrote in message
...
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?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.