Finding out of order numbers

[jimart]

Hi
I'm trying to find a way to query a field in a table & find any numbers that are not in numerical order & also the record number of the out of place number

Thanks

 

[llkhoutx]

You can't do it with a query, do it with a DAO/ADO recordset.

dim db as dao.database
dim rs as dao.recordset
dim iLastKey as long
dim rs2 as dao.recordset
set db=currentdb
'clear/empty tempary table
db.execute "DELETE * FROM tbTemp"
'tbTemp has one field of the same datatpye as your FieldInQuestion
set rs=db.openrecordset("tbYourTableName",dbopensnapshot)
set rs2=db.opersecordset("tbTemp",dbopendynaset)
iLastKey = ""
do until rs.eof
if rs!FieldInQuestion <> iLastKey + 1 then
'skip or out of order
'save field in question
rs2.addnew
rs2.fields(0) = rs!FieldInQuestion
rs2.update
end if
ilastkey=rs!FieldInQuestion
loop

rs.close
rs2.close
db.close

The foregoing outght to get you started. You don't say what you want to do the with gaps or out of orders.

 

[Pat Hartman]

The only way you can predict the order of a recordset is to sort it. Use a query with an order by clause instead of opening a table.