Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1143
  • Last Modified:

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?
0
HyperBPP
Asked:
HyperBPP
1 Solution
 
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
 
MacroShadowCommented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now