Solved

ADODB Recordset VBA only returning 255 characters.

Posted on 2013-12-18
2
1,048 Views
Last Modified: 2013-12-18
I have two queries for ADODB recordsets on a Excel workbook.   One query works fine:

Set rs = executeQuery( _
        "SELECT [my Requirement], title, text " & _
        "FROM " & _
            "[" & strTableName & "] WHERE [my Requirement] <> '' AND [my Requirement] <> '-' AND [myRequirement] = '" & CO & "'")
 
In the "text" field I can have well over 255 (current largest data is around 1000)

However, this query truncates text at 255:

Set rs = executeQuery( _
        "SELECT Distinct [my Requirement], text, [Reference], [Justification], [Impact],[CNA] " & _
        "FROM " & _
            "[" & strTableName & "] WHERE [my Requirement] = '-' AND [my Requirement] <> '-'")

I can't figure out why one works and the other doesnt?  Any thoughts?
0
Comment
Question by:HyperBPP
2 Comments
 
LVL 6

Author Comment

by:HyperBPP
ID: 39727774
So turns out, if I remove DISTINCT in the second query it works allright.  Not sure why this would be.  Unless it can't compare "memo" type?
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39727775
The problem with the second query is the Uniqueness, Since you asked the query to return only distinct values, Access must compare the memo field against all other records. The comparison causes truncation.

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

821 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