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

C

claven123

Board Regular
Joined
Sep 2, 2010
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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



VBA Code: 
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.
SQL: 
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
To do it in the query you'd use the TOP predicate, so SELECT TOP 22 ...

That's probably the better option.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I would just have a loop for 22 or EOF, then a loop for EOF at the new location.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
So i
claven123 said:
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
Click to expand...
So if you have 23 records, that is not going to work? :(
Look at what I posted in post #8
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Access UNION query won't run, data limits with a small file and almost nothing in it???
2
Replies
13
Views
4K
xenou
xenou
C
  • Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
1K
Trebor76
T
C
  • Question
compile error... method or data member not found
Replies
4
Views
1K
Norie
Norie
J
  • Question
Run the “ready” query in ACCESS from VBA Excel with the parameter
Replies
1
Views
2K
stumac
S
C
  • Question
export fields to excel from access
Replies
4
Views
660
Joe4
Joe4

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top