DOCUMENT:Q250640  11-JAN-2001  [vbwin]
TITLE   :PRB: GetChunk Ignores Offset if Memo Field Included in GROUP BY
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER::6.0
OPER/SYS:
KEYWORDS:kbJET kbVBp600 kbGrpDSVBDB kbDSupport kbDAO360bug

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Learning Edition for Windows, version 6.0 
 - Microsoft Visual Basic Professional Edition for Windows, version 6.0 
 - Microsoft Visual Basic Enterprise Edition for Windows, version 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

The Offset argument of the GetChunk method is used to set the number of bytes to
skip before copying begins within a Memo or Long Binary Field.

However, when the Memo field is included in the GROUP BY clause of the
recordset's SELECT statement, the Offset argument is being ignored. This causes
the characters from the beginning of the field to be retrieved instead of the
characters beginning at the Offset position. For example, if the Memo field
contains 500 characters and the Offset and Numbytes arguments are set to 250,
GetChunk returns the first 250 characters instead of the last 250 characters as
would be expected.

RESOLUTION
==========

Rewrite the query to eliminate all Memo fields from the GROUP BY clause. This
can be done by using an aggregate function on the Memo fields, such as the FIRST
function. This allows the Memo fields to be removed from the GROUP BY clause.
This workaround is illustrated in the example code below.

MORE INFORMATION
================

Microsoft Jet 4.0 has new functionality that allows you to use MEMO fields (Long
Varchar) in the GROUP BY clause of a SQL statement. This is not available in
earlier versions of the Jet database engine.

NOTE: The workaround SQL statement works with older versions of Jet.

Steps to Reproduce Behavior
---------------------------

1. Create a new Standard EXE project. Form1 is created by default.

2. On the Project menu, select References, and add a reference to the Microsoft
   DAO 3.6 object library.

3. Add the following code to the Form_Load() Event:

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim sQry As String
   Const conChunkSize = 60
   Dim lngOffset As Long
   Dim lngTotalSize As Long
   Dim strChunk As String
       
     Set dbsNorthwind = OpenDatabase("nwind.mdb")

     'Comment this line out to see the correct behavior.
     sQry = "SELECT FirstName, LastName, Notes FROM Employees " & _
            "GROUP BY FirstName, LastName, Notes"

     'Uncomment this line to see the correct behavior
     'sQry = "SELECT Employees.LastName, Employees.FirstName, First(Employees.Notes) " & _
             "AS Notes From Employees GROUP BY Employees.LastName, Employees.FirstName"

     Set rstEmployees = dbsNorthwind.OpenRecordset(sQry, dbOpenDynaset)
     Do While Not rstEmployees.EOF
       lngTotalSize = rstEmployees("Notes").FieldSize
       Do While lngOffset < lngTotalSize
         strChunk = rstEmployees("Notes").GetChunk(lngOffset, conChunkSize)
         Debug.Print strChunk
         lngOffset = lngOffset + conChunkSize
       Loop
     lngOffset = 0
     lngTotalSize = 0
     rstEmployees.MoveNext
     Loop

NOTE: You might have to adjust the path to NWIND.MDB.

4. The output from GetChunk is written to the Immediate Window, so make sure it
   is visible. If it is not, choose Immediate Window from the View menu.

5. Run the project, and note that the first 255 characters are repeated for all
   Memo fields that contain more than 255 characters.

6. Comment the first SQL statement and uncomment the second SQL statement.

7. Run the project, and note that you see the complete text of all Memo fields.

REFERENCES
==========

For additional information on how to use GetChunk, click the article number
below to view the article in the Microsoft Knowledge Base:

   Q210486 ACC2000: Reading, Storing, and Writing Binary Large Objects

Additional query words:

======================================================================
Keywords          : kbJET kbVBp600 kbGrpDSVBDB kbDSupport kbDAO360bug 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB600Search kbVBA600 kbVB600
Version           : :6.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.