Export data from access to Excel to multiple columns

[claven123]

I have 40 records that return from a query. I need to place 20 in column A and 20 in column D on export from acceess using VBA. I can get the entire 40 records in one column, but can't seem to figure out how to split them.

I've seen reference to LIMIT....OFFSET but not in Access VBA. I've seen a round about method using TOP in the SQL statement in the VBA, but not sure on the syntax. I've seen the TOP with percentage, but how would I limit the percentage on the second column?

D

 

[claven123]

My thought was to do it here. Say with GetRows(20) but did not work.

I have a SELECT statement that pulls the FullName in the rsActive. I could limit that and use 2 record sets. 1-20 and 21-40.

I've seen this type of stuff:

SELECT * FROM (SELECT TOP 50 tab2.* FROM (SELECT TOP 300 tab1.* FROM my_table AS tab1 ORDER BY column_name ASC) AS tab2 ORDER BY column_name DESC) ORDER BY column_name ASC;
This should return the records from row 250 to 300,
But that does not make much sense to me when I have to covert it to VBA sqlActive = "SELECT......

i = 8
With xlWks
    Do While Not rsActive.EOF
        .Range("C" & i).Value = (rsActive!FullName)
        i = i + 1
    rsActive.MoveNext
Loop

 

[claven123]

I was able to come up with this... however, it gives me the first 20 and then the other gives the last 20, but BACKWORDS.

Is there a way to do a subquery to reverse the order, I've read that there is, but I don't know the syntax with this type of query....

SQLActiveTop = "SELECT TOP 20 TblMembers.LastName, TblMembers.FirstName, TblMembers.Status, [FirstName] & "" "" & [LastName] AS FullName " & _
    "FROM TblMembers " & _
    "WHERE (((TblMembers.Status) = 'Active')) " & _
    "ORDER BY TblMembers.LastName ASC;"
SQLActiveBottom = "SELECT TOP 20 TblMembers.LastName, TblMembers.FirstName, TblMembers.Status, [FirstName] & "" "" & [LastName] AS FullName " & _
    "FROM TblMembers " & _
    "WHERE (((TblMembers.Status) = 'Active')) " & _
    "ORDER BY TblMembers.LastName DESC;"