ADODB Recordset VBA only returning 255 characters.

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?
LVL 6
HyperBPPAsked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
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
 
HyperBPPAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.