How do I select a range of records?
- Thread starter mark c
- Start date
If your records have an index which gives you the range value:
dim sql as string
sql = "INSERT INTO tbOutput SELECT tbInput.* FROM tbInput WHERE [Index] >" & 8000 & " And [Index]<=" & 15000"
currentdb.execute sql
If the Index has some known range, you could modify the above sql accordingly.
If your records do not have an index which gives the range, one kludgy method is:
DIM DAO.database
dim rsInput as dao.recordset
dim rsOutput as dao.recordset
set db=currentdb
rsInput - db.openrecord("tbInputTable",dbopensnapshot)
rsOutput - db.openrecord("tbOutputTable",dbopendynaset)
'skip first 7999 records
rsInput.move(7999)
dim ij as integer
'copy next 8000 records
for ij=1 to 7000
rsInput.movenext
rsOutput.Addnew
dim jk as integer
dim nFields as integer
nFields='an integer equaling the no. of fields being copied
for jk = 1 to nFields
rsOutput!fields(jk-1) = rsInput!fields(jk-1)
next
rsOutput.Update
next
dim sql as string
sql = "INSERT INTO tbOutput SELECT tbInput.* FROM tbInput WHERE [Index] >" & 8000 & " And [Index]<=" & 15000"
currentdb.execute sql
If the Index has some known range, you could modify the above sql accordingly.
If your records do not have an index which gives the range, one kludgy method is:
DIM DAO.database
dim rsInput as dao.recordset
dim rsOutput as dao.recordset
set db=currentdb
rsInput - db.openrecord("tbInputTable",dbopensnapshot)
rsOutput - db.openrecord("tbOutputTable",dbopendynaset)
'skip first 7999 records
rsInput.move(7999)
dim ij as integer
'copy next 8000 records
for ij=1 to 7000
rsInput.movenext
rsOutput.Addnew
dim jk as integer
dim nFields as integer
nFields='an integer equaling the no. of fields being copied
for jk = 1 to nFields
rsOutput!fields(jk-1) = rsInput!fields(jk-1)
next
rsOutput.Update
next
Last edited:
thanks for the reply but it's all greek to me. i wouldn't even know how to implement those commands.
I have two rows of data and need to select part of the left row. lots of records so it's hard to do manually. I would have to sit a few minutes holding shift + page down.. i'm looking for something like records>select range like in Excel..
I have two rows of data and need to select part of the left row. lots of records so it's hard to do manually. I would have to sit a few minutes holding shift + page down.. i'm looking for something like records>select range like in Excel..
ah
you need the right click menu!
Right click on the index field in the table. In the 'filter for' space put '>8000 AND <150000', without the quotes.
This filter for box can take any thing that would be a valid SQL WHERE clause. So if its a date field then 'BETWEEN' would work etc. If its text it defualts to being a like, so typing 'O*' in the box would filter everything starting with O.
To remove the filter right click again and 'remove filter/sort'
you need the right click menu!Right click on the index field in the table. In the 'filter for' space put '>8000 AND <150000', without the quotes.
This filter for box can take any thing that would be a valid SQL WHERE clause. So if its a date field then 'BETWEEN' would work etc. If its text it defualts to being a like, so typing 'O*' in the box would filter everything starting with O.
To remove the filter right click again and 'remove filter/sort'
Similar threads
