Microsoft KB Archive/250640

From BetaArchive Wiki

Article ID: 250640

Article Last Modified on 5/8/2003


  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition

This article was previously published under Q250640


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.


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.


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
      lngOffset = 0
      lngTotalSize = 0

    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.


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

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

Keywords: kbbug kbjet kbprb KB250640