Solved

ADODB Recordset VBA only returning 255 characters.

Posted on 2013-12-18
2
1,021 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 26

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now