I hope there is someone who can help me with a little problem I have been trying to solve for about a two weeks now.

Basically I have a list of customer ID's which are all the same format for example 1 letter followed by 6 numbers.

I get a long list of these numbers every day which are not in order.

I set the query to put the customer id's into ascending order.

Basically what I am trying to find are missing customer id’s. the criteria is that each customer id must be either the same as the previous customer id or a maximum of 1 number larger i.e. A000000 A000001 A000001 A000002 are all correct in ascending order but if A000009 was to follow I need to find a way to flag this and display what numbers are missing, as every number should be in sequence.

I managed to get this working in a module in excel

Sub one()
Max = InputBox(“Enter size of Spreadsheet”)
Base = Right (Sheet1.cells(2, 1), 6)
For lp = 3 To Val (Max)
Chk = Right(Sheet1.Cells(lp, 1), 6)
If ((chk – base) > 1) Then
S = “”
For q = base + 1 To chk – 1
S = s & Str(q) & vbCrLf
Next q
MsgBox (“Missing numbers” & vbCrLf & s)
End If
Base = chk
Next
MsgBox “Done”
End Sub.


As I am trying to get this working in Access 2003 I am unsure how to get the same response in a query in Access 2003.

I need this to check every record in access to make sure there are none missing. The above code I set to request the number of rows to check before it ran. The query and field details are as follows:

Query = TestQuery
Field = Customer_ID

You can see that the excel module also reads the 6 numbers from right to left and ignores the initial letter which is correct.

It also once finding a missing number or missing numbers searches for the next set.

Is there anyone that can help me with this as I am not that good with access and every attempt I have made to get this working in access has failed miserably?

I would write a query to:
Remove the Alpha character, leaving just the number
Check this against a table of numbers (numbers\ tally tables are SO useful yet woefully underused)
Return all numbers in the tally table not in your imported list of IDs

This would provide what you require - correct?

pootle flump ur codings are working excelent.

I have removed the initial character in previous attempts also I created a list of all customer id's is sequence, the problem this caused was that as customer A000102 could come through 3 time in a row each on a seperate record and not all customer id's come through each day they come through bit a time each day, so the records I am trying to compare is from the first record in the db to the last one received on each daily update.
I could get ten records one day and 200 the next. so the table to compare the records too flags up loads of errors.
As an Example the records in the db run from say A000503 and ever day new records are added and I was trying to find a way to highlight records that are missing from the sequence as each new recorded added should be either the same as the previous or 1 number higher than the previous.
I dont know enough about access to get my excel module to work in access. I dont even know if it is possible.
I hope im not too confusing, I started to loose myself then.
Thank you again for your quick reply

Look at the "DISTINCT" keyword in Access/Jet sql. Then look in to the "numbers table" that pootle was mentioning.

I think I didnt read what was written correctly I am going to have a play and see if I can get this to work.
I dont really know that much about access im a cobol sort of person.

thank you both for you help.

After some playing I eneded up with this and it is working perfectly.

Sub one()
Dim rs As ADODB.Recordset
DoCmd.OpenQuery "TestQuery"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * From TestQuery", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs.MoveFirst
base = Mid(rs("Customer_ID"), 2)
rs.MoveNext
While Not rs.EOF
chk = Mid(rs("Customer_ID"), 2)
If ((chk - base) > 1) Then
s = ""
For q = base + 1 To chk - 1
s = s & Right("000000" & Trim(Str(q)), 6) & vbCrLf
Next q
MsgBox ("Missing numbers" & vbCrLf & s)
End If
base = chk
rs.MoveNext
Wend
MsgBox "Done"
End Sub

Thank you all for your help.