Select from record x to record y

[microdoc]

I need a quick program that will select from record x to record y from an existing query and export the result as a text file.

Example: Select from record 1000 to record 2000 from query and export to select.txt

If i can get some assistance, i can probably figure out the vb code to do this on a form, but feel free to provide suggestions.

 

[Golom]

Record 1000" or "Record 2000" have meaning only if you have a field that contains the relevant values. SQL has no guaranteed ordering of it's output records if you don't have an ORDER BY clause so there is no pre-determined "Record 1000" (or whatever).

One possibility is an AutoNumber field so that you could do something like

Select * From tbl 
Where Anum BETWEEN 1000 and 2000

That may not give you the exact result that you're looking for because of the way that AutoNumbers are assigned by the DBMS.

The other possibility is to retrieve the query into a recordset and use the ordering that the recordset assigns. Something like

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset ( "myQuery" )
rs.MoveFirst
rs.Move 1000
n = 1000
Do Until n = 2000
    ' *** Export the record to Text ***
    rs.MoveNext
    n = n + 1
Loop

omitting the code to test for EOF and whatnot.

 

[microdoc]

I have the query set up with the relavent field sorted already, so all I need to do is take that big list of 30,000 and extract roughly 1000 at a time.

 

[Golom]

As I said ... Access doesn't have a record number that is returned as part of a query. If you pull the query into a recordset as in the example, then the recordset (among other things) builds a record number that you can use via the ".Move" method.

You can expand on the sample code I gave you to select whatever group(s) of records that you want. Note that ".Move" is relative to the current record.