Solved

Full string not returned on RS.open command

Posted on 2016-10-20
7
35 Views
Last Modified: 2016-10-21
Access 2013, desktop
A query called queryname returns the value of the field Cri6Comment among others.

When I run the query by itself  - it returns the full length of Cri6Comment - so far so good.
Cri6Comment has about say 1000 characters

When i do the following
dim string1 as string
rs.open "Select * from queryname ", currentproject.connection, adopenstatic  adlockreadonly
debug.print rs!Cri6Comment \

The value is it is truncated ( suspect to 255 chars)  

When I do
String1 = rs!cri6comment

String1 is truncated from what should be the true value of Cri6Comment.

Why is the query truncating my field?  

Cri6comment is Long Test
0
Comment
Question by:Keyboard Cowboy
7 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41852919
is there a line feed in the original text.
0
 

Author Comment

by:Keyboard Cowboy
ID: 41852932
I don't think so.  It happens on several fields and they all display properly in a form
cri6comment is just one of them.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 41852947
what is the SQL statement of the query "queryName"?

are you using aggregate function in the query?

try opening the table as recordset and see if the field will be truncated to 255

dim string1 as string
 rs.open "Select * from TABLEname ", currentproject.connection, adopenstatic  adlockreadonly
 debug.print rs!Cri6Comment
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 125 total points
ID: 41853268
try using DAO instead of ADO
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 125 total points
ID: 41853967
You might need to invoke the getchunk method on such fields.

reference: https://msdn.microsoft.com/en-us/library/ms681747(v=vs.85).aspx
0
 

Author Comment

by:Keyboard Cowboy
ID: 41853989
I discovered that the query wasn't returning the full text field.  There are several situtations where a query will truncate a long text field - such as using DISTINCT with a long text field will truncate it to 255 chars).  However, none of those applied to me.

I fixed the problem by copying the query from an old backup and it started working.
Whew...
0
 

Author Closing Comment

by:Keyboard Cowboy
ID: 41853991
Thanks everyone -
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question