Limit record set to 22 and then 23-end of file

[claven123]

I am exporting data from access to excel. I have a record set that is larger than the space (rows), I'd like to place the first 22 records and then place the remaining records a few columns over. I'm not sure how to limit the first set of data ? array

i = 16
With xlWks
    Do While Not rsHonorary.EOF
        .Range("AB" & i).Value = Nz(rsHonorary!FullName, "")
        i = i + 1
    rsHonorary.MoveNext
    Loop
End With

This is the query that provides the data.

SQLHonorary = "SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Status, [FirstName] & "" "" & [LastName] AS FullName " & _
    "FROM TblMembers " & _
    "WHERE (((TblMembers.Status) = 'Honorary')) " & _
    "ORDER BY TblMembers.LastName, TblMembers.FirstName;"

Would I limit the record set at the query level or the placement in Excel?

D

 

[Micron]

To do it in the query you'd use the TOP predicate, so SELECT TOP 22 ...

That's probably the better option.

 

[claven123]

Thanks for the key word searches on TOP, I've figure out the top 22, which works fine. The second part is the 23 to n records. I've seen some code on using two top recordsets and dividing etc... Is there a simple manner to select records in the middle of the record set?

d

 

[claven123]

Does rsHonorary in the code above have a set order, say 1-37? It's not sorted at this point. Can I limit the movenext to 1-22? Then resume 23 to next at a different column?

D

 

[welshgasman]

I would just have a loop for 22 or EOF, then a loop for EOF at the new location.

 

[Micron]

I didn't grasp the meaning of 'place remaining records a few columns over' - thought that was something else. To use a recordset you'd first need to get the count and base 2 loops on that count unless it would only ever be 45 in total. You'd also have to use an ordered set, otherwise the records may not be in the same order each time, but ordering by name columns could be OK. You could also run 2 sql statements; 1 for top 22 ordered ascending, 1 for top 23 ordered descending.

 

[claven123]

I'm not quite sure how to do that (loop a specific number of records). I found a solution or work around was to put the whole list on another sheet and 'move' the data over to the other sheet, which does work. I forgot the list is ordered.

 

[welshgasman]

Do until iLoop > 22 or rs.eof
Do your stuff
Add 1 to iloop


Repeat second loop until eof.
If you always have more than 22 records, then you can skip the first eof check.

 

[claven123]

This is what I currently have.

i = 1
With xlWks
Do While Not rsHonorary.EOF
.Range("A" & i).Value = Nz(rsHonorary!FullName, "")
i = i + 1
rsHonorary.MoveNext
Loop
End With

 

[welshgasman]

So i

This is what I currently have.

i = 1
With xlWks
Do While Not rsHonorary.EOF
.Range("A" & i).Value = Nz(rsHonorary!FullName, "")
i = i + 1
rsHonorary.MoveNext
Loop
End With

So if you have 23 records, that is not going to work?
Look at what I posted in post #8