I am an access user with a fairly limited knowledge of coding hence using queries, reports and to a limited extent functions as provided with access 2003. How do I go about to find missing numbers in a series of numbers between 600 and 800, i.e. numbers 671 - 677 are missing or number 798 is missing. Would appreciate if someone could point me in the right direction. Thanks, Wilfried

Dim Database As Database Dim Recordset As Recordset Dim SQLSelect As String Dim Recordcount As Long Dim RecordArray() As Integer Dim Item As Long Dim RecordCounter As Long SQLSelect = "SELECT * from TableName WHERE Number => 6000 and Number =< 8000 Order by Number" Set Database = OpenDatabase("DataFileName", False, False) Set Recordset = Database.OpenRecordset(Sele) Recordset.MoveLast Recordset.MoveFirst Recordcount = Recordset.Recordcount ReDim RecordArray(Recordcount) Do While Not Recordset.EOF If RecordCounter <> Recordset("Number") Then RecordArray(Item) = Recordset("number") Item = Item + 1 End If RecordCounter = RecordCounter + 1 Recordset.MoveNext Loop Recordset.Close Database.Close Some code to get you started. This uses DOA. Using VBA, open your database and using the SQLSelect string, retrieve data with records 600-600 contained in the field name number. Move to last last record and then back to the first. This ensures that the record count is accurate. This will give you the number of records retrieved from which you can deduce the number of records missing. Use the number to define an array with the number of elements of missing records. You then iterate through the records using the variable RecordCounter. When a record is found the does not have a corresponding number it is inserted into the RecordArray and the array counter item is incremented. At the end of the loop the array should contain all the missing numbers. The variables are declared as long. If you know for definite that there will never be more than 32,000 records, you can declare them as Integer. I have not tested this code and it may not work perfectly. But it will give you and idea of what is required. Stuart

Hello Stuart, Thank you very much for your quick response. I will give this one a go over the weekend. I will keep you posted on my progress. Greetings from downunder, Wilfried

Hello Stuart, I have been trying now for some weeks to get your code and/or variations thereof working, but my knowledge of VBA seems to be too little. There are two basic problems, firstly I can't get the code right without compile error and secondly I am not sure as to how to invoke the code so it runs. That's were I am up to now: Option Compare Database Dim MissingNos As Recordset Dim sqlselect As String Dim recordcount As Long Dim recordarray() As Integer Dim item As Long Dim recordcounter As Long Private Sub FindMember_Click() Set Database = OpenDatabase("d:\data\access\work\FTC - Membership 2005_06 Design.mdb", False, False) Set MissingNos = Database.OpenRecordset(MissingNos) MissingNos.MoveLast MissingNos.MoveFirst recordcount = MissingNos.recordcount ReDim recordarray(recordcount) sqlselect = "SELECT * from members where [Receipt Number] =>600 and [Receipt Number] <= 800 order by [Receipt Number]" Do While Not MissingNos.EOF If recordcounter <> MissingNos("Receipt Number") Then recordarray(item) = MissingNos("Receipt Number") item = item + 1 End If recordcounter = recordcounter + 1 Recordset.MoveNext Loop Recordset.Close Database.Close End Sub Please bear with me for a little longer, I am still on it and hopefully will get somewhere soon. Will keep you posted. Option Compare Database Dim MissingNos As Recordset Dim sqlselect As String Dim recordcount As Long Dim recordarray() As Integer Dim item As Long Dim recordcounter As Long Private Sub FindMember_Click() Set Database = OpenDatabase("d:\data\access\work\FTC - Membership 2005_06 Design.mdb", False, False) Set MissingNos = Database.OpenRecordset(MissingNos) MissingNos.MoveLast MissingNos.MoveFirst recordcount = MissingNos.recordcount ReDim recordarray(recordcount) sqlselect = "SELECT * from members where [Receipt Number] =>600 and [Receipt Number] <= 800 order by [Receipt Number]" Do While Not MissingNos.EOF If recordcounter <> MissingNos("Receipt Number") Then recordarray(item) = MissingNos("Receipt Number") item = item + 1 End If recordcounter = recordcounter + 1 Recordset.MoveNext Loop Recordset.Close Database.Close End Sub

Hello Stuart, I have been trying now for some weeks to get your code and/or variations thereof working, but my knowledge of VBA seems to be too little. There are two basic problems, firstly I can't get the code right without compile error and secondly I am not sure as to how to invoke the code so it runs. That's were I am up to now: Option Compare Database Dim MissingNos As Recordset Dim sqlselect As String Dim recordcount As Long Dim recordarray() As Integer Dim item As Long Dim recordcounter As Long Private Sub FindMember_Click() Set Database = OpenDatabase("d:\data\access\work\FTC - Membership 2005_06 Design.mdb", False, False) Set MissingNos = Database.OpenRecordset(MissingNos) MissingNos.MoveLast MissingNos.MoveFirst recordcount = MissingNos.recordcount ReDim recordarray(recordcount) sqlselect = "SELECT * from members where [Receipt Number] =>600 and [Receipt Number] <= 800 order by [Receipt Number]" Do While Not MissingNos.EOF If recordcounter <> MissingNos("Receipt Number") Then recordarray(item) = MissingNos("Receipt Number") item = item + 1 End If recordcounter = recordcounter + 1 Recordset.MoveNext Loop Recordset.Close Database.Close End Sub Please bear with me for a little longer, I am still on it and hopefully will get somewhere soon. Will keep you posted. Regards, Wilfried