Microsoft KB Archive/250640

= PRB: GetChunk Ignores Offset if Memo Field Is Included in GROUP BY =

Article ID: 250640

Article Last Modified on 5/8/2003

-

APPLIES TO


 * 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



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
 Create a new Standard EXE project. Form1 is created by default. On the Project menu, select References, and add a reference to the Microsoft DAO 3.6 object library.  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.

 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. Run the project, and note that the first 255 characters are repeated for all Memo fields that contain more than 255 characters. Comment the first SQL statement and uncomment the second SQL statement.</li> Run the project, and note that you see the complete text of all Memo fields.</li></ol>

<div class="references_section">